library(tidyverse)
library(haven)
library(formatR)
library(lubridate)
library(smooth)
library(forecast)
library(scales)
library(kableExtra)
library(ggplot2)
library(readxl)
library(tidyverse)
library(data.table)
library(quantmod)
library(geofacet)
library(janitor)
knitr::opts_chunk$set(echo = TRUE, warning = FALSE, message = FALSE)
exp_temp <- read_csv("exp_temp.csv")
rev_temp <- read_csv("rev_temp.csv")# check what is being included in pensions
# funds related to pension contributions
pension_funds <- c("0472", "0473", "0477", "0479", "0481", "0755", "0786", "0787", "0788", "0789", "0799")
pension_check <- exp_temp %>%
mutate(pension = case_when(
# object == "4430" & fund == "0825" ~ "Object 4430 - Pension Buyout/Benefits Paid Early",
(object=="4430") ~ "Object 4430 - Benefits Paid to Employees; EXCLUDED", # pensions, annuities, benefits
(object=="4431") ~ "Object 4431 - State Contributions; INCLUDED", # 4431 = state payments into pension fund
(obj_seq_type > "11590000" & obj_seq_type < "11660000") ~ "Object 1160-1165 Employer Contributions to Pension Fund; EXCLUDED",
# objects 1159 to 1166 are all considered Retirement by Comptroller
TRUE ~ "0")) %>% # All other observations coded as 0 for non-pension items
# recodes specific instances of code anomalies from past years:
mutate(pension = case_when(
(object=="1298" & fund %in% pension_funds ) ~ "Object 1298 - Purchase of Investments; DROPPED",
# pension stabilization fund in 2022
# object == "1900" & fund == "0319" ~ "Fund 0319-Pension Stabilization",
object == "1900" & fund %in% pension_funds ~ "Fund 0319 - Pension Stabilization",
object == "4900" & fund %in% pension_funds ~ "Object 4900 - Awards/Grants; Weird 2010-2011 values",
TRUE ~ as.character(pension)) ) %>%
filter(pension != "0" )
pension_check %>% group_by(fy, pension) %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
ggplot(aes(x=fy, y = expenditure, color = pension)) +
geom_line() +
labs (title = "Pension Fund Payments In and Retirement Benefits Out",
caption = "Object 4430 is retirement benefits paid to employees.
Object 4431 includes state payments INTO pension Fund.
Object 1998 is excluded except for years 2010 and 2011 due to POBs.")+
theme(legend.position = "bottom")+
guides(color = guide_legend(nrow=3))
pension_check %>% group_by(fy, object) %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
ggplot(aes(x=fy, y = expenditure, color = object)) +
geom_line() +
labs (title = "Expenditures by Object")
pension_check %>% group_by(fy, type) %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
ggplot(aes(x=fy, y = expenditure, color = type)) +
geom_line() +
labs (title = "Expenditures by Type", caption = "Not confident with what Type represents.
$10 billion POB issued in 2003-2004 and again in 2010-2011.")
pension_check3 <- exp_temp %>%
mutate(pension = case_when(
(object=="4430" ) ~ 1, # 4430 = pension benefits paid to retired employees
TRUE ~ 0)) %>%
filter(pension > 0 )
pension_check3 %>% group_by(fy) %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
ggplot(aes(x=fy, y = expenditure)) +
geom_line() +
labs (title = "Pension Benefits Paid to Employees")
## taking care of Pension Obligation Bond proceeds
pension_picture <- exp_temp %>%
mutate(pension = case_when(
#object == "4430" & fund == "0825" ~ "Pension Buyout/Benefits Paid Early; INCLUDED",
(object=="4430") ~ "Benefits Paid to Employees", # pensions, annuities, benefits
(object=="4431") ~ "State Pension Contributions", # 4431 = state payments into pension fund
(obj_seq_type > "11590000" & obj_seq_type < "11660000") ~ "IOC Retirement Expense Objectw",
# objects 1159 to 1166 are all considered Retirement by Comptroller
TRUE ~ "0")) %>% # All other observations coded as 0 for non-pension items
# recodes specific instances of code anomalies from past years:
# mutate(pension = case_when( (object=="1298" & fund %in% pension_funds ) ~ "Purchase of Investments",
# pension stabilization fund in 2022
# object == "1900" & fund == "0319" ~ "Fund 0319-Pension Stabilization",
# object == "1900" & fund %in% pension_funds ~ "Pension Stabilization Fund",
#TRUE ~ as.character(pension)) ) %>%
filter(pension != "0" )
pension_picture %>% group_by(fy, pension) %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
ggplot(aes(x=fy, y = expenditure, color = pension)) +
geom_line() +
labs (title = "Pension Fund Payments In and Retirement Benefits Out",
caption = "All pension expenditure types are included in IOC Expenditure data")+
theme(legend.position = "bottom", legend.title = element_blank())#+ guides(color = guide_legend(nrow=2))
# rev_type = 51 is for retirement/pension contributions from both employers and employees.
# current year employee revenue source = 0573, contributions by employee == 572 (stops at 2011)
retirement_contributions <- rev_temp %>%
filter(rev_type == "51") %>% group_by(fy) %>% summarize(contributions = sum(receipts))
employer_contributions <- rev_temp %>%
filter(rev_type == "51" & source == "0577") %>% group_by(fy) %>% summarize(contributions = sum(receipts))
employee_contributions <- rev_temp %>%
filter(rev_type == "51" & (source == "0572" | source == "0573") ) %>%
group_by(fy) %>% summarize(contributions = sum(receipts))
benefits_paid <- pension_check %>% filter(object == "4430") %>%
group_by(fy) %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE))
state_contrib <- pension_check %>% filter(object == "4431") %>%
group_by(fy) %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE))
rev_temp %>%
filter(rev_type == "51") %>% # all retirement contributions
group_by(fy, source) %>%
summarise(sum = sum(receipts, na.rm = TRUE)) %>%
ggplot() +
geom_line(aes(x=fy, y = sum, color=source)) + labs(title="All Retirement Contributions, ALL rev_source == 51",
caption = "Source 0573, 0572 is for employee contributions. 0577 is Contributions by employer.")
# # contributions and benefits paid comparison
# ggplot()+
# geom_line(data=employee_contributions, aes(x=fy, y=contributions), color=" light green") +
# geom_line(data=employer_contributions, aes(x=fy, y=contributions), color="orange") +
#
# geom_line(data= state_contrib, aes(x=fy, y = expenditure), color = "red")+
#
# geom_line(data= benefits_paid, aes(x=fy, y = expenditure), color = "dark blue")+
# labs(title="Pension fund inflows and outflows",
# caption = "Blue: object = 4430 for benefits paid out of funds,
# red: object = 4431 for state contributions into pension fund,
# neon green: employee contributions into the fund,
# orange: employer contributions into the fund.", y = "Dollars")
pension_picture %>% group_by(fy, pension) %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
ggplot(aes(x=fy, y = expenditure, color = pension)) +
geom_line() +
geom_line(data=employee_contributions, aes(x=fy, y=contributions), color="green") +
geom_line(data=employer_contributions, aes(x=fy, y=contributions), color="orange") +
labs (title = "Pension Fund Payments In and Retirement Benefits Out",
caption = "Neon green - employee contributions INTO the fund.
Orange - employer contributions INTO the fund.")+
theme(legend.position = "bottom", legend.title = element_blank())
tollway <- exp_temp %>% filter(fund == "0455") #all tollway expenditures
capitalproject_debtservice <- exp_temp %>%filter(object == "8800") # ALL Capital projects debt service
# look at Illinois tollway bond proceeds and debt service:
# rev_temp %>% filter(fund == "0455") # examine items in fund 0455
#exp_temp %>% filter(fund == "0455") %>% group_by(fy) %>% summarize(sum = sum(expenditure)) %>% arrange(-fy)
tollway#rev_temp %>% filter(fund == "0455") %>% group_by(fy) %>% summarize(sum = sum(receipts)) %>% arrange(-fy)
tollway_exp <- exp_temp %>% filter(fund == "0455") %>% group_by(fy) %>% summarize(expenditure = sum(expenditure))
#tollway_exp %>% ggplot() + geom_line(aes(x=fy, y=expenditure)) + labs(title = "Fund 0455 from Expenditure: All Tollway Expenditures", caption = "Data from IOC Expenditure Files. Fund 0455 is the IL State Tollway")
# all tollway revenues, not just bond proceeds
alltollway<-rev_temp %>% filter(fund == "0455" & source != "0571") %>% group_by(fy) %>% summarize(sum = sum(receipts, na.rm = TRUE))
# tollway bond proceeds
tollway_bondproc <- rev_temp %>% filter(fund == "0455" & source == "0571" ) %>% group_by(fy) %>% summarize(sum = sum(receipts, na.rm = TRUE))
#alltollway %>% ggplot() + geom_line(aes(x=fy, y=sum)) + labs(title = "Fund 0455 - All Tollway Revenue", caption = "Data from IOC Revenue Files. Fund 0455 is the IL State Tollway Revenue")
#tollway_bondproc %>% ggplot() + geom_line(aes(x=fy, y=sum)) + labs(title = "Fund 0455 - Tollway Revenue: Tollway Bond Proceeds", caption = "Data from IOC Revenue Files. Fund 0455 is the IL State Tollway Revenue")
#ggplot() + geom_line(data=tollway_bondproc, aes(x=fy, y=sum)) + labs(title = "Fund 0455 - Tollway Revenue: Tollway Bond Proceeds", caption = "Data from IOC Revenue Files. Fund 0455 is the IL State Tollway Revenue")
#tollwaydebt %>% ggplot() + geom_line(aes(x=fy, y=sum)) + labs(title = "Tollway Debt Service", caption = "Debt service includes principal and interest for the Illinois Tollway. Object = 8800 and fund = 0455")
#tollway debt principal and interest
tollwaydebt <- exp_temp %>%filter(object == "8800" & fund == "0455") %>% group_by(fy) %>% summarize(sum=sum(expenditure))
# Tollway agency expenditures = SAME as filtering by fund == 0455
#tollway<-exp_temp %>% filter(agency == "557")
#exp_temp %>% filter(agency == "557") %>% group_by(fy) %>% summarize(sum = sum(expenditure)) %>% arrange(-fy)
# contributions and benefits paid comparison
ggplot()+
geom_line(data=tollway_bondproc, aes(x=fy, y=sum, color='Bond Proceeds')) +
geom_line(data= tollwaydebt, aes(x=fy, y = sum, color = 'Debt Service'))+
geom_line(data= tollway_exp, aes(x=fy, y = expenditure, color = 'Tollway Expenditures'))+
geom_line(data= alltollway, aes(x=fy, y = sum, color = "Tollway Revenue"))+
scale_color_manual(values = c(
'Bond Proceeds' = 'darkblue',
'Debt Service' = 'red',
'Tollway Expenditures' = 'orange',
'Tollway Revenue' = 'light green')) +
labs(title="Tollway bond procreeds, debt service, revenue, and expenditures.",
caption = "Tollway revenue + bond proceeds should be roughly equal to tollway expenditures + debt service.",
y = "Dollars")
Filtering for interest on short term borrowing and GO bonds (88130008, 88130000, and 88130108) and GO bond principal amounts (88110008).
object == 8813 is for all debt service interest but obj_seq_type is used to specify short term borrowing versus regular debt service.
an Interest to Principal ratio is also calculated in the table below.
Looking only at general obligation principal payments and interest payments:
# GO bond principal and GO bond interest
GObond_debt <- exp_temp %>%
filter(obj_seq_type == "88110008" |obj_seq_type == "88130000" | obj_seq_type == "88130008") %>%
group_by(fy, obj_seq_type) %>%
summarize(sum = sum(expenditure, na.rm=TRUE)) %>%
pivot_wider(names_from = obj_seq_type, values_from = sum) %>%
mutate(principal = `88110008`,
interest = sum(`88130008`+`88130000`, na.rm = TRUE),
ratio = (as.numeric(interest)/as.numeric(principal)))
GObond_debt %>% select(principal, interest, ratio) %>%
mutate(across(principal:interest, ~format(., big.mark= ",", scientific = F)))# GObond_debt %>% ggplot() +
# geom_line(aes(x=fy, y=principal, color = "Principal"))+
# geom_line(aes(x=fy, y=interest, color = "Interest")) +
# labs(title = "General Obligation principal and interest payments")
GObond_debt %>% ggplot() +
geom_col(aes(x=fy, y=interest/1000000, fill = "Interest")) +
geom_col(aes(x=fy, y=principal/1000000, fill = "Principal"))+
labs(title = "Debt Service", subtitle = "General Obligation Principal and Interest Payments")
Looking only at short term borrowing principal and interest payments:
# short term borrowing, first observation is in 2004?
short_debt <- exp_temp %>%
filter(obj_seq_type == 88110108 |obj_seq_type == 88130108) %>%
group_by(fy, obj_seq_type) %>%
summarize(sum = sum(expenditure, na.rm=TRUE)) %>%
pivot_wider(names_from = obj_seq_type, values_from = sum) %>%
mutate(principal = `88110108`,
interest = `88130108`,
ratio = (as.numeric(interest)/as.numeric(principal)))
short_debt %>% select(principal, interest, ratio) %>%
mutate(across(principal:interest, ~format(., big.mark= ",", scientific = F)))short_debt %>% ggplot() +
geom_col(aes(x=fy, y=principal/1000000, fill = "Principal"))+
geom_col(aes(x=fy, y=interest/1000000, fill = "Interest")) +
labs(title = "Debt Service", subtitle = "Short Term Borrowing: Principal and Interest Payments")
When including short term borrowing and normal debt service, the debt ratio seems more normal and the total interest and principal payments over the years are smoothed out.
Principal and interest amounts calculated exclude the Illinois Tollway debt service and debt for capital projects. Capital projects debt service is examined below:
capitalprojects <- exp_temp %>%filter(object == "8800")
all_debt <- exp_temp %>%
filter(fund != "0455" & (object == "8811" |object == "8813" | object == "8800") )%>%
group_by(fy, object) %>%
summarize(sum = sum(expenditure, na.rm=TRUE)) %>%
pivot_wider(names_from = object, values_from = sum) %>%
mutate(principal = `8811`,
interest = `8813`,
CapitalProjects = `8800`,
ratio = (as.numeric(interest)/as.numeric(principal)))
all_debt %>% select(principal, interest, CapitalProjects, ratio) %>%
mutate(across(principal:CapitalProjects, ~format(., big.mark= ",", scientific = F)))all_debt %>% ggplot() +
geom_line(aes(x=fy, y=principal/1000000, color = "Principal"))+
geom_line(aes(x=fy, y=interest/1000000, color = "Interest"))+
geom_line(aes(x=fy, y = CapitalProjects / 1000000, color = "Capital Projects"))+
labs(y = "Debt ($Millions)",
title = "Principal and Interest payments", subtitle = "Principal and interest from short term borrowing and GO Bonds debt service", caption = "Capital projects does not include Illinois tollway debt service.
Capital projects include interest and principal values as one value and cannot be sepearated.")
all_debt %>% ggplot() +
geom_line(aes(x=fy, y=principal/1000000, color = "Principal"))+
geom_line(aes(x=fy, y=interest/1000000, color = "Interest"))+
geom_line(aes(x=fy, y = CapitalProjects / 1000000, color = "Capital Projects Debt Service"))+
geom_line(data = tollwaydebt, aes( x=fy, y=sum/1000000, color = "Tollway Debt Service"))+
labs(y = "Debt ($Millions)", title = "Short term borrowing and GO Bonds",
subtitle = "Principal and Interest payments", caption = "Capital projects does not include Illinois tollway debt service.") 
all_debt %>% ggplot() +
geom_line(aes(x=fy, y=(principal+interest)/1000000, color = "Principal & Interest"))+
#geom_line(aes(x=fy, y=interest/1000000, color = "Interest"))+
geom_line(aes(x=fy, y = CapitalProjects / 1000000, color = "Capital Projects Debt Service"))+
geom_line(data = tollwaydebt, aes( x=fy, y=sum/1000000, color = "Tollway Debt Service"))+
labs(y = "Debt ($Millions)", title = "Illinois Debt Service Expenditures: Short term borrowing and GO Bonds",
subtitle = "Principal and Interest payments", caption = "Capital projects does not include Illinois tollway debt service.") 
Capital projects include the IL Civic Center and Build Illinois Bonds. Tollway principal and interest has been dropped from the State’s Debt Service expenditure but is counted in the Illinois Tollway Expenditure cost.
health_ins_reserve <- exp_temp %>% filter(fund == "0907") %>% group_by(fy) %>%
summarize(fund_0907 = sum(expenditure))
health_ins_reserve %>%
ggplot(aes(x=fy, y=fund_0907)) + geom_line() + labs(title="Health Insurance Reserve", subtitle = "Sum of expenditures from fund 907")
# object 1180 is inconsistently coded over time form the IOC
# object 1180 should be employer contributions to healthcare group insurance
employer_contributions <- exp_temp %>% filter(object == "1180") %>% group_by(fy) %>% summarize(object1180 = sum(expenditure))
employer_contributions%>%
ggplot(aes(x=fy, y=object1180)) + geom_line() + labs(title="Employer Contributions to Healthcare Group Insurance, IOC Object 1180")
employer_contributions2 <- exp_temp %>% filter(object == "1180" & fund=="0001") %>% group_by(fy) %>% summarize(object1180 = sum(expenditure))
employer_contributions2 %>%
ggplot(aes(x=fy, y=object1180)) + geom_line() + labs(title="Employer Contributions to Healthcare Group Insurance", subtitle = "IOC Object 1180 from Fund 001")
# examine combined group insurance totals per year
group_ins2 <- exp_temp %>%
mutate(eehc = ifelse(
# group insurance contributions for 1998-2005 and 2013-present
# CMS took over health insurance in 2013
fund == "0001" & (object == "1180" | object =="1900") & agency == "416" & appr_org=="20", 1, 0) )%>%
mutate(eehc = ifelse(
# group insurance contributions for 2006-2012
# health insurance was in healthcare and family services, agency 478 for a few years
fund == "0001" & object == "1180" & agency == "478" & appr_org=="80", 1, eehc) )%>%
filter(eehc == 1) %>%
group_by(fy) %>%
summarize(dropped_group_premiums = sum(expenditure, na.rm=TRUE))
group_ins2 %>% ggplot(aes(x=fy, y=dropped_group_premiums)) + geom_line() + labs(title="Employer Healthcare Group Insurance Contributions", subtitle= " - Dropped from analysis to avoid double counting healthcare expenditures", caption = "Objects 1180 and 1900 from fund 0001. See code for additional coding details.")
health_insurance_fund_rev<- rev_temp %>%
filter(fund=="0907") %>%
group_by(fy) %>%
summarize(health_ins_rev = sum(receipts))
health_insurance_fund_rev %>%
ggplot(aes(x=fy, y = health_ins_rev)) +
geom_line() + labs( title = "Health insurance fund - All revenue, Fund 0907")
#collect optional insurance premiums to fund 0907 for use in eehc expenditure
employee_health_premiums <- rev_temp %>%
mutate(employee_premiums = ifelse(
fund=="0907" & (source=="0120"| source=="0121"| (source>"0345" & source<"0357")|(source>"2199" & source<"2209")), 1, 0)) %>%
filter(employee_premiums == 1)
# optional insurance premiums = employee insurance premiums
emp_premium <- employee_health_premiums %>%
group_by(fy) %>%
summarize(employee_premiums_sum = sum(receipts))
emp_premium %>% ggplot(aes(x=fy, y = employee_premiums_sum)) +
geom_line() + labs( title = "Employee health insurance premiums")
# contributions and benefits paid comparison
ggplot()+
# geom_line(data=group_ins, aes(x=fy, y=object1180, color='Group Insurance1')) +
geom_line(data=health_insurance_fund_rev, aes(x=fy, y=health_ins_rev, color='Health Insurance Fund - All Revenue')) +
geom_line(data = emp_premium, aes(x=fy, y = employee_premiums_sum, color = 'Revenue from Employee Premiums')) +
geom_line(data=health_ins_reserve, aes(x=fy, y=fund_0907, color='Cost of Provision')) +
geom_line(data=employer_contributions, aes(x=fy, y=object1180, color='Group Insurance-Object1180')) +
# geom_line(data=employer_contributions2, aes(x=fy, y=object1180, color='Employer Contributions-General Fund')) +
geom_line(data=group_ins2, aes(x=fy, y=dropped_group_premiums, color='Group Insurance - 1180 & 1900')) +
#geom_line(data= healthcare_costs, aes(x=fy, y = cost_of_provision, color = 'Healthcare Costs'))+
scale_color_manual(values = c(
'Cost of Provision' = 'darkblue',
'Health Insurance Fund - All Revenue' = 'light green',
'Revenue from Employee Premiums' = 'dark green',
'Group Insurance - 1180 & 1900' = 'blue',
'Group Insurance-Object1180' = 'light blue'
# 'Employer Contributions-General Fund' = 'light blue'
)) +
labs(title="Healthcare costs and group insurance contributions",
caption = "Healthcare costs and group insurance contributions",
y = "Dollars", x = "")
exp_temp <- exp_temp %>%
mutate(eehc = ifelse(object == "1180", 1, 0)) %>%
mutate(eehc = ifelse((eehc == 1 & in_ff =="0"), 2, eehc))
#%>%mutate(in_ff = ifelse(eehc == 2, "1", in_ff) ) %>% filter(eehc ==2 )
table(exp_temp$eehc)
0 1 2
163720 4414 149 medicaid_cost <- exp_temp %>% filter(agency=="478" & (appr_org=="01" | appr_org == "65" | appr_org=="88") & (object=="4900" | object=="4400")) %>% group_by(fy) %>% summarize(sum=sum(expenditure))
med_reimburse <- rev_temp %>% filter(rev_type=="57" & agency=="478" & (source=="0618"|source=="2364"|source=="0660"|source=="1552"| source=="2306"| source=="2076"|source=="0676"|source=="0692")) %>% group_by(fy) %>% summarize(sum=sum(receipts))
ggplot()+
geom_line(data=medicaid_cost, aes(x=fy, y=sum), color = "red") +
geom_line(data=med_reimburse, aes(x=fy, y = sum), color="black") + labs(title = "Medicaid reimbursements and Medicaid expenditures", caption = "Medicaid expenditures include funds provided to medical providers. ")
Aggregate expenditures: Save tax refunds as negative revenue. Code refunds to match the rev_type codes (02=income taxes, 03 = corporate income taxes, 06=sales tax, 09=motor fuel tax, 24=insurance taxes and fees, 35 = all other tax refunds).
## negative revenue becomes tax refunds
tax_refund_long <- exp_temp %>% # fund != "0401" # removes State Trust Funds
filter(fund != "0401" & (object=="9910"|object=="9921"|object=="9923"|object=="9925")) %>%
# keeps these objects which represent revenue, insurance, treasurer,and financial and professional reg tax refunds
mutate(refund = case_when(
fund=="0278" & sequence == "00" ~ "02", # for income tax refund
fund=="0278" & sequence == "01" ~ "03", # tax administration and enforcement and tax operations become corporate income tax refund
fund == "0278" & sequence == "02" ~ "02",
object=="9921" ~ "21", # inheritance tax and estate tax refund appropriation
object=="9923" ~ "09", # motor fuel tax refunds
obj_seq_type == "99250055" ~ "06", # sales tax refund
fund=="0378" & object=="9925" ~ "24", # insurance privilege tax refund
fund=="0001" & object=="9925" ~ "35", # all other taxes
T ~ "CHECK")) # if none of the items above apply to the observations, then code them as CHECK
exp_temp <- left_join(exp_temp, tax_refund_long) %>%
mutate(refund = ifelse(is.na(refund),"not refund", as.character(refund)))
tax_refund <- tax_refund_long %>%
group_by(refund, fy)%>%
summarize(refund_amount = sum(expenditure, na.rm = TRUE)/1000000) %>%
pivot_wider(names_from = refund, values_from = refund_amount, names_prefix = "ref_") %>%
mutate_all(~replace_na(.,0)) %>%
arrange(fy)
tax_refund %>% pivot_longer( ref_02:ref_35, names_to = "Refund Type", values_to = "Amount") %>%
ggplot()+
geom_line(aes(x=fy,y=Amount, group = `Refund Type`, color = `Refund Type`))+
labs(title = "Refund Types", caption = "Refunds are excluded from Expenditure totals and instead subtracted from Revenue totals") +
labs(title = "Tax refunds",
caption = "Rev_type codes: 02=income taxes, 03=corporate income taxes, 06=sales tax, 09=motor fuel tax,
24=insurance taxes and fees, 35 = all other tax refunds." )
# remove the items we recoded in tax_refund_long
exp_temp <- exp_temp %>% filter(refund == "not refund")tax_refund amounts are removed from expenditure totals and subtracted from revenue totals (since they were tax refunds).
State payments to the following pension systems:
• Teachers Retirement System (TRS)
- New POB bond in 2019: Accelerated Bond Fund paid benefits in advance as lump sum
• State Employee Retirement System (SERS)
• State University Retirement System (SURS)
• Judges Retirement System (JRS)
• General Assembly Retirement System (GARS)
Modify exp_temp and move all state pension contributions to their own group (901):
exp_temp <- exp_temp %>%
arrange(fund) %>%
mutate(pension = case_when(
(object=="4431") ~ 1, # 4431 = easy to find pension payments INTO fund
# (object>"1159" & object<"1166") & fund != "0183" & fund != "0193" ~ 2,
# objects 1159 to 1166 are all considered Retirement by Comptroller,
# Excluded - employer contributions from agencies/organizations/etc.
(object=="1298" & # Purchase of Investments, Normally excluded
(fy==2010 | fy==2011) &
(fund=="0477" | fund=="0479" | fund=="0481")) ~ 3, #judges retirement OUT of fund
# state borrowed money from pension funds to pay for core services during 2010 and 2011.
# used to fill budget gap and push problems to the future.
fund == "0319" ~ 4, # pension stabilization fund
TRUE ~ 0) )
table(exp_temp$pension)
0 1 3 4
167875 228 6 5 exp_temp %>% filter(pension != 0) %>%
mutate(pension = as.factor(pension))%>%
group_by(fy, pension) %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
ggplot(aes(x=fy, y = expenditure, group=pension)) +
geom_line(aes(color = pension)) +
labs (title = "Pension expenditures",
caption = "1 = State contributions INTO pension funds")+
theme(legend.position = "bottom")
# special accounting of pension obligation bond (POB)-funded contributions to JRS, SERS, GARS, TRS
exp_temp <- exp_temp %>%
# change object for 2010 and 2011, retirement expenditures were bond proceeds and would have been excluded
mutate(object = ifelse((pension >0 & in_ff == "0"), "4431", object)) %>%
# changes weird teacher & judge retirement system pensions object to normal pension object 4431
mutate(pension = ifelse(pension >0 & in_ff == "0", 6, pension)) %>% # coded as 6 if it was supposed to be excluded.
mutate(in_ff = ifelse(pension>0, "1", in_ff))
table(exp_temp$pension)
0 1 4 6
167875 226 5 8 # all other pensions objects codes get agency code 901 for State Pension Contributions
exp_temp <- exp_temp %>%
mutate(agency = ifelse(pension>0, "901", as.character(agency)),
agency_name = ifelse(agency == "901", "State Pension Contributions", as.character(agency_name)))
exp_temp %>%
filter(pension > 0) %>%
mutate(pension = as.factor(pension)) %>%
group_by(fy, pension) %>%
summarize(expenditure = sum(expenditure, na.rm=TRUE)) %>%
ggplot(aes(x=fy, y=expenditure, color = pension)) +
geom_line() +
labs (title = "Pension Expenditures",
caption = "")
exp_temp %>%
filter(pension > 0) %>%
group_by(fy) %>%
summarize(expenditure = sum(expenditure, na.rm=TRUE)) %>%
ggplot(aes(x=fy, y=expenditure)) +
geom_line() +
labs (title = "Pension Expenditures")
transfers_drop <- exp_temp %>% filter(
agency == "799" | # statutory transfers
object == "1993" | # interfund cash transfers
object == "1298") # purchase of investments
exp_temp <- anti_join(exp_temp, transfers_drop)
exp_temptransfers_drop %>% filter(fy>2019 & object == 1993) %>% group_by(obj_seq_type) %>% summarize(sum = sum(expenditure)) %>% arrange(-sum)If observation is a group insurance contribution, then the expenditure amount is set to $0 (essentially dropped from analysis).
#if observation is a group insurance contribution, then the expenditure amount is set to $0 (essentially dropped from analysis)
# pretend eehc is named group_insurance_contribution or something like that
# eehc coded as zero implies that it is group insurance
# if eehc=0, then expenditures are coded as zero for group insurance to avoid double counting costs
exp_temp <- exp_temp %>%
mutate(eehc = ifelse(
# group insurance contributions for 1998-2005 and 2013-present
fund == "0001" & (object == "1180" | object =="1900") & agency == "416" & appr_org=="20", 0, 1) )%>%
mutate(eehc = ifelse(
# group insurance contributions for 2006-2012
fund == "0001" & object == "1180" & agency == "478" & appr_org=="80", 0, eehc) )%>%
# group insurance contributions from road fund
# coded with 1900 for some reason??
mutate(eehc = ifelse(
fund == "0011" & object == "1900" & agency == "416" & appr_org=="20", 0, eehc) ) %>%
mutate(expenditure = ifelse(eehc=="0", 0, expenditure)) %>%
mutate(agency = case_when( # turns specific items into State Employee Healthcare (agency=904)
fund=="0907" & (agency=="416" & appr_org=="20") ~ "904", # central management Bureau of benefits using health insurance reserve
fund=="0907" & (agency=="478" & appr_org=="80") ~ "904", # agency = 478: healthcare & family services using health insurance reserve - stopped using this in 2012
TRUE ~ as.character(agency))) %>%
mutate(agency_name = ifelse(agency == "904", "STATE EMPLOYEE HEALTHCARE", as.character(agency_name)),
in_ff = ifelse( agency == "904", 1, in_ff),
group = ifelse(agency == "904", "904", as.character(agency)))
# creates group variable
# Default group = agency number
healthcare_costs <- exp_temp %>% filter(group == "904")
healthcare_costsexp_temp %>% filter(group == "904") %>% group_by(fy) %>%
summarise(healthcare_cost = sum(expenditure, na.rm = TRUE)) %>%
ggplot() +geom_line(aes(x=fy, y=healthcare_cost)) + labs(title="State Employee Healthcare Costs - Included in Fiscal Futures Model", caption = "Fund 0907 for agencies responsible for health insurance reserve (DHFS & CMS)")
#exp_temp <- anti_join(exp_temp, healthcare_costs) %>% mutate(expenditure = ifelse(object == "1180", 0, expenditure))
#healthcare_costs_yearly <- healthcare_costs %>% group_by(fy, group) %>% summarise(healthcare_cost = sum(expenditure, na.rm = TRUE)/1000000) %>% select(-group)Separate transfers to local from parent agencies that come from DOR(492) or Transportation (494). Treats muni revenue transfers as expenditures, not negative revenue.
The share of certain taxes levied state-wide at a common rate and then transferred to local governments. (Purely local-option taxes levied by specific local governments with the state acting as collection agent are NOT included.)
The six corresponding revenue items are:
• Local share of Personal Income Tax - Individual Income Tax Pass-Through New 2021 (source 2582). • Local share of General Sales Tax
• Personal Property Replacement Tax on Business Income
• Personal Property Replacement Tax on Public Utilities
• Local share of Motor Fuel Tax - Transportation Renewal Fund 0952
Until Dec 18. 2022, Local CURE was being aggregated into Revenue totals since the agency was the Department of Revenue. However the $371 million expenditure is for “LOC GOVT ARPA” and the revenue source that is Local CURE is also $371 million. Since it cancels out and is just passed through the state government, I am changing changing the fund_ab_in file so that in_ff=0 for the Local CURE fund. It also inflates the department of revenue expenditures in a misleading way when the expense is actually a transfer to local governments.
exp_temp <- exp_temp %>% mutate(
agency = case_when(fund=="0515" & object=="4470" & type=="08" ~ "971", # income tax to local governments
fund=="0515" & object=="4491" & type=="08" & sequence=="00" ~ "971", # object is shared revenue payments
fund=="0802" & object=="4491" ~ "972", #pprt transfer
fund=="0515" & object=="4491" & type=="08" & sequence=="01" ~ "976", #gst to local
fund=="0627" & object=="4472"~ "976" , # public transportation fund but no observations exist
fund=="0648" & object=="4472" ~ "976", # downstate public transportation, but doesn't exist
fund=="0515" & object=="4470" & type=="00" ~ "976", # object 4470 is grants to local governments
object=="4491" & (fund=="0188"|fund=="0189") ~ "976",
fund=="0187" & object=="4470" ~ "976",
fund=="0186" & object=="4470" ~ "976",
object=="4491" & (fund=="0413"|fund=="0414"|fund=="0415") ~ "975", #mft to local
fund == "0952"~ "975", # Added Sept 29 2022 AWM. Transportation Renewal MFT
TRUE ~ as.character(agency)),
agency_name = case_when(agency == "971"~ "INCOME TAX 1/10 TO LOCAL",
agency == "972" ~ "PPRT TRANSFER TO LOCAL",
agency == "975" ~ "MFT TO LOCAL",
agency == "976" ~ "GST TO LOCAL",
TRUE~as.character(agency_name)),
group = ifelse(agency>"970" & agency < "977", as.character(agency), as.character(group)))transfers_long <- exp_temp %>%
filter(group == "971" |group == "972" | group == "975" | group == "976")
transfers_long %>%
group_by(agency_name, group, fy) %>%
summarize(expenditure = sum(expenditure, na.rm=TRUE) )%>%
ggplot() + geom_line(aes(x=fy, y = expenditure, color=agency_name)) + labs(title = "Transfers to Local Governments", caption = "Data Source: Illinois Office of the Comptroller")
transfers <- transfers_long %>%
group_by(fy, group ) %>%
summarize(sum_expenditure = sum(expenditure)/1000000) %>%
pivot_wider(names_from = "group", values_from = "sum_expenditure", names_prefix = "exp_" )
exp_temp <- anti_join(exp_temp, transfers_long)
dropped_inff_0 <- exp_temp %>% filter(in_ff == 0)
exp_temp <- exp_temp %>% filter(in_ff == 1) # drops in_ff = 0 funds AFTER dealing with net-revenue aboveThe Local Transfers from the Personal Property Replacement Tax (fund 802) increased over $2 billion from corporate income taxes alone. Personal property replacement taxes (PPRT) are revenues collected by the state of Illinois and paid to local governments to replace money that was lost by local governments when their powers to impose personal property taxes on corporations, partnerships, and other business entities were taken away.
Debt Service expenditures include interest payment on both short-term and long-term debt. We do not include escrow or principal payments.
Decision from Sept 30 2022: We are no longer including short term principal payments as a cost; only interest on borrowing is a cost. Pre FY22 and the FY21 correction, we did include an escrow payment and principle payments as costs but not bond proceeds as revenues. This caused expenditures to be inflated because we were essentially counting debt twice - the principle payment and whatever the money was spent on in other expenditure categories, which was incorrect.
debt_drop <- exp_temp %>%
filter(object == "8841" | object == "8811")
# escrow OR principle
#debt_drop %>% group_by(fy) %>% summarize(sum = sum(expenditure)) %>% arrange(-fy)
debt_keep <- exp_temp %>%
filter(fund != "0455" & (object == "8813" | object == "8800" ))
# examine the debt costs we want to include
#debt_keep %>% group_by(fy) %>% summarize(sum = sum(expenditure)) %>% arrange(-fy)
exp_temp <- anti_join(exp_temp, debt_drop)
exp_temp <- anti_join(exp_temp, debt_keep)
debt_keep <- debt_keep %>%
mutate(
agency = ifelse(fund != "0455" & (object == "8813" | object == "8800"), "903", as.character(agency)),
group = ifelse(fund != "0455" & (object == "8813" | object == "8800"), "903", as.character(group)),
in_ff = ifelse(group == "903", 1, as.character(in_ff)))
debt_keep_yearly <- debt_keep %>% group_by(fy, group) %>% summarize(debt_cost = sum(expenditure,na.rm=TRUE)/1000000) %>% select(-group)Medicaid. That portion of the Healthcare and Family Services (or Public Aid in earlier years, agency code 478) budget for Medical (appr_organization code 65) for awards and grants (object codes 4400 and 4900).
State CURE will remain in the Medicaid expenditure category due to the nature of it being federal funds providing public health services and funding to locations that provide public services.
Federal Medical Assistance Program (FMAP): in 1965. The FMAP formula compares the state per-capita income to the national per-capita income. There is no cap on the dollar amount that the federal government pays, so the morethat a state spends the more that it receives. a maximum of 83%. States with a higher per-capita income receive lower FMAP funding but no less than 50%, and the states that have a lower per-capita income receive higher FMAP funding. Those that need more, get more.
exp_temp <- exp_temp %>%
#mutate(agency = as.numeric(agency) ) %>%
# arrange(agency)%>%
mutate(
group = case_when(
agency>"100"& agency<"200" ~ "910", # legislative
agency == "528" | (agency>"200" & agency<"300") ~ "920", # judicial
pension>0 ~ "901", # pensions
(agency>"309" & agency<"400") ~ "930", # elected officers
agency == "586" ~ "959", # create new K-12 group
agency=="402" | agency=="418" | agency=="478" | agency=="444" | agency=="482" ~ as.character(agency), # aging, CFS, HFS, human services, public health
T ~ as.character(group))
) %>%
mutate(group = case_when(
agency=="478" & (appr_org=="01" | appr_org == "65" | appr_org=="88") & (object=="4900" | object=="4400") ~ "945", # separates CHIP from health and human services and saves it as Medicaid
agency == "586" & fund == "0355" ~ "945", # 586 (Board of Edu) has special education which is part of medicaid
# OLD CODE: agency == "586" & appr_org == "18" ~ "945", # Spec. Edu Medicaid Matching
agency=="425" | agency=="466" | agency=="546" | agency=="569" | agency=="578" | agency=="583" | agency=="591" | agency=="592" | agency=="493" | agency=="588" ~ "941", # public safety & Corrections
agency=="420" | agency=="494" | agency=="406" | agency=="557" ~ as.character(agency), # econ devt & infra, tollway
agency=="511" | agency=="554" | agency=="574" | agency=="598" ~ "946", # Capital improvement
agency=="422" | agency=="532" ~ as.character(agency), # environment & nat. resources
agency=="440" | agency=="446" | agency=="524" | agency=="563" ~ "944", # business regulation
agency=="492" ~ "492", # revenue
agency == "416" ~ "416", # central management services
agency=="448" & fy > 2016 ~ "416", #add DoIT to central management
T ~ as.character(group))) %>%
mutate(group = case_when(
# agency=="684" | agency=="691" ~ as.character(agency), # moved under higher education in next line. 11/28/2022 AWM
agency=="692" | agency=="695" | agency == "684" |agency == "691" | (agency>"599" & agency<"677") ~ "960", # higher education
agency=="427" ~ as.character(agency), # employment security
agency=="507"| agency=="442" | agency=="445" | agency=="452" |agency=="458" | agency=="497" ~ "948", # other departments
# other boards & Commissions
agency=="503" | agency=="509" | agency=="510" | agency=="565" |agency=="517" | agency=="525" | agency=="526" | agency=="529" | agency=="537" | agency=="541" | agency=="542" | agency=="548" | agency=="555" | agency=="558" | agency=="559" | agency=="562" | agency=="564" | agency=="568" | agency=="579" | agency=="580" | agency=="587" | agency=="590" | agency=="527" | agency=="585" | agency=="567" | agency=="571" | agency=="575" | agency=="540" | agency=="576" | agency=="564" | agency=="534" | agency=="520" | agency=="506" | agency == "533" ~ "949",
# non-pension expenditures of retirement funds moved to "Other Departments"
# should have removed pension expenditures already from exp_temp in Pensions step above
agency=="131" | agency=="275" | agency=="589" |agency=="593"|agency=="594"|agency=="693" ~ "948",
T ~ as.character(group))) %>%
mutate(group_name =
case_when(
group == "416" ~ "Central Management",
group == "478" ~ "Healthcare and Family Services",
group == "482" ~ "Public Health",
group == "900" ~ "NOT IN FRAME",
group == "901" ~ "STATE PENSION CONTRIBUTION",
group == "903" ~ "DEBT SERVICE",
group == "910" ~ "LEGISLATIVE" ,
group == "920" ~ "JUDICIAL" ,
group == "930" ~ "ELECTED OFFICERS" ,
group == "940" ~ "OTHER HEALTH-RELATED",
group == "941" ~ "PUBLIC SAFETY" ,
group == "942" ~ "ECON DEVT & INFRASTRUCTURE" ,
group == "943" ~ "CENTRAL SERVICES",
group == "944" ~ "BUS & PROFESSION REGULATION" ,
group == "945" ~ "MEDICAID" ,
group == "946" ~ "CAPITAL IMPROVEMENT" ,
group == "948" ~ "OTHER DEPARTMENTS" ,
group == "949" ~ "OTHER BOARDS & COMMISSIONS" ,
group == "959" ~ "K-12 EDUCATION" ,
group == "960" ~ "UNIVERSITY EDUCATION" ,
group == agency ~ as.character(group),
TRUE ~ "Check name"),
year = fy)
exp_temp %>% filter(group_name == "Check name")#write_csv(exp_temp, "all_expenditures_recoded.csv")All expenditures recoded but not aggregated: Allows for inspection of individual expenditures within larger categories. This stage of the data is extremely useful for investigating almost all questions we have about the data.
Note that these are the raw figures BEFORE we take the additional steps:
exp_temp %>% filter(fy>2020 & fund == "0561") %>% group_by(wh_approp_name, fy) %>% summarize(sum=sum(expenditure)) %>% arrange(-sum)SBE Federal Department of Education is fund 0561. Fund 0579 is the State Board of Education.
Revenue Categories NOT included in Fiscal Futures:
- 32. Garnishment-Levies. (State is fiduciary, not beneficiary.)
- 45. Student Fees-Universities. (Excluded from state-level budget.)
- 51. Retirement Contributions (of individuals and non-state entities).
- 66. Proceeds, Investment Maturities. (Not sustainable flow.)
- 72. Bond Issue Proceeds. (Not sustainable flow.)
- 75. Inter-Agency Receipts.
- 79. Cook County Intergovernmental Transfers. (State is not beneficiary.)
- 98. Prior Year Refunds.
- 99. Statutory Transfers.
All Other Sources
Expanded to include the following smaller sources:
- 30. Horse Racing Taxes & Fees.
- 60. Other Grants and Contracts.
- 63. Investment Income.
For aggregating revenue, use the rev_1998_2022 dataframe, join the funds_ab_in_2022 file to it, and then join the ioc_source_type file to the dataset. Remember: You need to update the funds_ab_in and ioc_source_type file every year!
# recodes old agency numbers to consistent agency number
rev_temp <- rev_temp %>%
mutate(agency = case_when(
(agency=="438"| agency=="475" |agency == "505") ~ "440",
# financial institution & professional regulation &
# banks and real estate --> coded as financial and professional reg
agency == "473" ~ "588", # nuclear safety moved into IEMA
(agency =="531" | agency =="577") ~ "532", # coded as EPA
(agency =="556" | agency == "538") ~ "406", # coded as agriculture
agency == "560" ~ "592", # IL finance authority (fire trucks and agriculture stuff)to state fire marshal
agency == "570" & fund == "0011" ~ "494", # city of Chicago road fund to transportation
TRUE ~ (as.character(agency)))) #rev_temp <- rev_temp %>% filter(in_ff==1)
rev_temp <- rev_temp %>%
mutate(
rev_type = ifelse(rev_type=="57" & agency=="478" & (source=="0618"|source=="2364"|source=="0660"|source=="1552"| source=="2306"| source=="2076"|source=="0676"|source=="0692"), "58", rev_type),
rev_type_name = ifelse(rev_type=="58", "Federal Medicaid Reimbursements", rev_type_name),
rev_type = ifelse(rev_type=="57" & agency=="494", "59", rev_type),
rev_type_name = ifelse(rev_type=="59", "Federal Transportation", rev_type_name),
rev_type_name = ifelse(rev_type=="57", "Federal - Other", rev_type_name),
rev_type = ifelse(rev_type=="6", "06", rev_type),
rev_type = ifelse(rev_type=="9", "09", rev_type))
rev_temp %>%
filter(rev_type == "58" | rev_type == "59" | rev_type == "57") %>%
group_by(fy, rev_type, rev_type_name) %>%
summarise(receipts = sum(receipts, na.rm = TRUE)/1000000) %>%
ggplot() +
geom_line(aes(x=fy, y=receipts,color=rev_type_name)) +
theme_bw() +
scale_y_continuous(labels = comma)+
labs(title = "Federal to State Transfers",
y = "Millions of Dollars", x = "") +
theme(legend.position = "bottom", legend.title = element_blank() )
Looking at Federal Revenue:
All revenue sources within “Federal - Other” source.
rev_temp %>% filter(rev_type == "57" & fy >2018) %>% group_by(fund_name, source_name_AWM, fy) %>% summarize(receipts =sum(receipts)) %>% arrange(-receipts) %>% pivot_wider(names_from = fy, values_from = receipts)fed_rev_compare <- rev_temp %>% filter((rev_type == "57" | rev_type == "58" | rev_type == "59") & (fy == 2022 | fy==2021 | fy==2020 | fy == 2019)) %>% arrange(-receipts)
write_csv(fed_rev_compare, "comparefedrev.csv")
rev_temp %>% filter(source_name_AWM == "FEDERAL STIMULUS PACKAGE") %>% group_by(fy, fund_name) %>% summarize(receipts =sum(receipts)) %>% arrange(-fy)rev_temp %>% filter(fy > 2018 & source_name_AWM == "FEDERAL STIMULUS PACKAGE") %>% group_by(fund_name, fy) %>% summarize(receipts =sum(receipts)) %>% arrange(-receipts)rev_temp %>% filter(rev_type == "57" & fy > 2018 & fund_name == "SBE FEDERAL DEPT OF EDUCATION") %>% group_by(source_name_AWM , fund_name, fy) %>% summarize(receipts =sum(receipts)) %>% arrange(-receipts)exp_temp %>% filter(fy >2019 & (fund_name == "STATE CURE" | fund_name == "LOCAL CURE" | fund_name == "SBE FEDERAL DEPT OF EDUCATION" | fund_name == "DISASTER RESPONSE AND RECOVERY" | fund_name == "ESSENTIAL GOVT SERV SUPPORT" )) %>% group_by(fy, agency_name, wh_approp_name, fund_name) %>%
summarize(sum=sum(expenditure),
appropriated = sum(appn_net_xfer)) %>%
arrange(-appropriated)exp_temp %>% filter(fy >2019 & (fund_name == "STATE CURE" | fund_name == "LOCAL CURE" | fund_name == "SBE FEDERAL DEPT OF EDUCATION" | fund_name == "DISASTER RESPONSE AND RECOVERY" | fund_name == "ESSENTIAL GOVT SERV SUPPORT" )) %>% group_by(fy, wh_approp_name, fund_name) %>%
summarize(sum=sum(expenditure),
appropriated = sum(appn_net_xfer)) %>%
arrange(-appropriated)exp_temp %>% filter(fy >2019 & (fund_name == "STATE CURE" | fund_name == "LOCAL CURE" | fund_name == "SBE FEDERAL DEPT OF EDUCATION" | fund_name == "DISASTER RESPONSE AND RECOVERY" | fund_name == "ESSENTIAL GOVT SERV SUPPORT" )) %>% group_by(fund_name, fy, agency_name) %>%
summarize(sum=sum(expenditure),
appropriated = sum(appn_net_xfer)) %>%
arrange(-appropriated)exp_temp %>% filter(fy == 2022 & (fund_name == "STATE CURE" | fund_name == "LOCAL CURE")) %>% group_by(org_name, agency_name, object, wh_approp_name, fund_name) %>% summarize(sum=sum(expenditure)) %>% arrange(-sum)exp_temp %>% filter(fy == 2022 & (fund_name == "STATE CURE" | fund_name == "LOCAL CURE")) %>% group_by(agency_name, object, wh_approp_name, fund_name) %>% summarize(sum=sum(expenditure)) %>% arrange(-sum)exp_temp %>% filter(fy == 2022 & (fund_name == "STATE CURE" | fund_name == "LOCAL CURE")) %>% group_by(fund_name, object, org_name) %>% summarize(sum=sum(expenditure)) %>% arrange(-sum)exp_temp %>% filter(fy == 2022 & (fund_name == "STATE CURE" | fund_name == "LOCAL CURE")) %>% group_by(fund_name, agency_name) %>% summarize(sum=sum(expenditure)) %>% arrange(-sum)exp_temp %>% filter(fy == 2022 & (fund_name == "STATE CURE" | fund_name == "LOCAL CURE")) %>% group_by(agency_name) %>% summarize(sum=sum(expenditure)) %>% arrange(-sum)exp_temp %>% filter(fy == 2021 & (fund_name == "STATE CURE" | fund_name == "LOCAL CURE")) %>% group_by(wh_approp_name, fund_name) %>% summarize(sum=sum(expenditure)) %>% arrange(-sum)Dropping State CURE Revenue
The Fiscal Futures model focuses on sustainable revenue sources. To understand our fiscal gap and outlook, we need to exclude these one time revenues. GOMB has emphasized that they have allocated COVID dollars to one time expenditures (unemployment trust fund, budget stabilization fund, etc.). The fiscal gap, graphs,and CAGRs have been recalculated in the Drop COVID Dollars section below. In addition, an attempt at forecasting revenue and expenditures is also made after dropping the federal COVID dollars.
NOTE: I have only dropped State and Local CURE revenue so far. Federal money went into other funds during the beginning of pandemic.
rev_temp <- rev_temp %>% mutate(covid_dollars = ifelse(source_name_AWM == "FEDERAL STIMULUS PACKAGE",1,0))Funds that hold and disperse local taxes or fees are dropped from the analysis. Then other excluded revenue types are also dropped.
Drops Blank, Student Fees, Retirement contributions, proceeds/investments, bond issue proceeds, interagency receipts, cook IGT, Prior year refunds:
I don’t have much faith in the transfers in and out steps- AWM
I am currently choosing to exclude the totals from this step. Overall, this decreases the total revenues in “All Other Revenues” by a few million dollars.
rev_temp <- rev_temp %>%
filter(in_ff == 1) %>%
mutate(local = ifelse(is.na(local), 0, local)) %>% # drops all revenue observations that were coded as "local == 1"
filter(local != 1)
# 1175 doesnt exist?
in_from_out <- c("0847", "0867", "1175", "1176", "1177", "1178", "1181", "1182", "1582", "1592", "1745", "1982", "2174", "2264")
# what does this actually include:
# all are items with rev_type = 75 originally.
in_out_df <- rev_temp %>%
mutate(infromout = ifelse(source %in% in_from_out, 1, 0)) %>%
filter(infromout == 1)
rev_temp <- rev_temp %>%
mutate(rev_type_new = ifelse(source %in% in_from_out, "76", rev_type))
# if source contains any of the codes in in_from_out, code them as 76 (all other rev).
# I end up excluding rev_76 in later stepsCorporate income tax Individual Income Tax Pass-Through (source =2582) was over 2 billion. The PTE tax allows a workaround to the federal $10,000 limitation for state and local tax (SALT) deductions and expires Jan 1. 2026 (to correspond with remaining years that the Tax Cuts and Jobs Act SALT limitation is in effect) Tax Adviser. With the enactment of the Tax Cuts and Jobs Act of 2017 (“TCJA”), individual taxpayers were limited to a $10,000 state and local tax deduction per year. In response to this limitation, many states created a workaround mechanism, introducing a pass-through entity tax (“PTET”). This shifted the state and local tax deduction from an individual taxpayer to the entity level that is not subject to the $10,000 limitation. Implications: Illinois residents in multistate passthrough entities will need to pay estimated taxes on income that is not subject to the SALT cap tax. TCJA of 2017 decreased
# revenue types to drop
drop_type <- c("32", "45", "51",
"66", "72", "75", "79", "98")
# drops Blank, Student Fees, Retirement contributions, proceeds/investments,
# bond issue proceeds, interagency receipts, cook IGT, Prior year refunds.
rev_temp <- rev_temp %>% filter(!rev_type_new %in% drop_type)
# keep observations that do not have a revenue type mentioned in drop_type
table(rev_temp$rev_type_new)
02 03 06 09 12 15 18 21 24 27 30 31 33
161 124 828 127 575 258 45 1420 450 76 659 124 130
35 36 39 42 48 54 57 58 59 60 63 76 78
660 5152 9044 2755 31 1239 6450 620 226 103 5081 154 11261
99
963 rev_temp %>%
group_by(fy, rev_type_new) %>%
summarize(total_reciepts = sum(receipts)/1000000) %>%
pivot_wider(names_from = rev_type_new, values_from = total_reciepts, names_prefix = "rev_") # combines smallest 4 categories to to "Other"
# they were the 4 smallest in past years, are they still the 4 smallest?
rev_temp <- rev_temp %>%
mutate(rev_type_new = ifelse(rev_type=="30" | rev_type=="60" | rev_type=="63" | rev_type=="76", "78", rev_type_new))
#table(rev_temp$rev_type_new) # check work
rm(rev_1998_2022)
rm(exp_1998_2022)
#write.csv(exp_temp, "exp_fy22_recoded_12192022.csv")
#write.csv(rev_temp, "rev_fy22_recoded_12192022.csv")Local Government Transfers (exp_970) should be on the expenditure side
State employer group insurance contributions should be dropped to avoid double counting both the state. Do not do this. This was done for FY21 only and will not be done again.
Subtract employee insurance premiums from State Employee Healthcare Expenditures (group == 904) - Employee Premiums = Actual state healthcare costs.
ff_exp\(exp904 − emp_premium\)employee_premiums_sum = statehealthcarecosts
I chose to drop rev_76 for Transfers in and Out because I do not understand why that step occurs in the previously used Stata code. Rev_76 was created and included in rev_78 for All Other Revenues in old Stata code for years before FY21 but that method has been discontinued for FY22.
ff_rev <- rev_temp %>%
group_by(rev_type_new, fy) %>%
summarize(sum_receipts = sum(receipts, na.rm=TRUE)/1000000 ) %>%
pivot_wider(names_from = "rev_type_new", values_from = "sum_receipts", names_prefix = "rev_")
ff_rev<- left_join(ff_rev, tax_refund)
#ff_rev <- left_join(ff_rev, pension2_fy22, by=c("fy" = "year"))
#ff_rev <- left_join(ff_rev, eehc2_amt)
ff_rev <- mutate_all(ff_rev, ~replace_na(.,0))
ff_rev <- ff_rev %>%
mutate(rev_02 = rev_02 - ref_02,
rev_03 = rev_03 - ref_03,
rev_06 = rev_06 - ref_06,
rev_09 = rev_09 - ref_09,
rev_21 = rev_21 - ref_21,
rev_24 = rev_24 - ref_24,
rev_35 = rev_35 - ref_35
# rev_78new = rev_78 #+ pension_amt #+ eehc
) %>%
select(-c(ref_02:ref_35, rev_99, rev_NA, rev_76#, pension_amt , rev_76,
# , eehc
))
ff_revSince I already pivot_wider()ed the table in the previous code chunk, I now change each column’s name by using rename() to set new variable names. Ideally the final dataframe would have both the variable name and the variable label but I have not done that yet.
aggregate_rev_labels <- ff_rev %>%
rename("INDIVIDUAL INCOME TAXES, gross of local, net of refunds" = rev_02,
"CORPORATE INCOME TAXES, gross of PPRT, net of refunds" = rev_03,
"SALES TAXES, gross of local share" = rev_06 ,
"MOTOR FUEL TAX, gross of local share, net of refunds" = rev_09 ,
"PUBLIC UTILITY TAXES, gross of PPRT" = rev_12,
"CIGARETTE TAXES" = rev_15 ,
"LIQUOR GALLONAGE TAXES" = rev_18,
"INHERITANCE TAX" = rev_21,
"INSURANCE TAXES&FEES&LICENSES, net of refunds" = rev_24 ,
"CORP FRANCHISE TAXES & FEES" = rev_27,
# "HORSE RACING TAXES & FEES" = rev_30, # in Other
"MEDICAL PROVIDER ASSESSMENTS" = rev_31 ,
# "GARNISHMENT-LEVIES " = rev_32 , # dropped
"LOTTERY RECEIPTS" = rev_33 ,
"OTHER TAXES" = rev_35,
"RECEIPTS FROM REVENUE PRODUCNG" = rev_36,
"LICENSES, FEES & REGISTRATIONS" = rev_39 ,
"MOTOR VEHICLE AND OPERATORS" = rev_42 ,
# "STUDENT FEES-UNIVERSITIES" = rev_45, # dropped
"RIVERBOAT WAGERING TAXES" = rev_48 ,
# "RETIREMENT CONTRIBUTIONS " = rev_51, # dropped
"GIFTS AND BEQUESTS" = rev_54,
"FEDERAL OTHER" = rev_57 ,
"FEDERAL MEDICAID" = rev_58,
"FEDERAL TRANSPORTATION" = rev_59 ,
#"OTHER GRANTS AND CONTRACTS" = rev_60, #other
# "INVESTMENT INCOME" = rev_63, # other
# "PROCEEDS,INVESTMENT MATURITIES" = rev_66 , #dropped
# "BOND ISSUE PROCEEDS" = rev_72, #dropped
# "INTER-AGENCY RECEIPTS" = rev_75, #dropped
# "TRANSFER IN FROM OUT FUNDS" = rev_76, #other
"ALL OTHER SOURCES" = rev_78,
# "COOK COUNTY IGT" = rev_79, #dropped
# "PRIOR YEAR REFUNDS" = rev_98 #dropped
)
aggregate_rev_labelsCreate exp_970 for all local government transfers (exp_971 + exp_972 + exp_975 + exp_976).
Create state employee healthcare costs that reflects the health costs minus the optional insurance premiums that came in (904_new=904−med_option_amt_recent). Do not do this. This was done for FY21 only and will not be done again. Small differences in overall Fiscal Gap from methodology change.
ff_exp <- exp_temp %>%
group_by(fy, group) %>%
summarize(sum_expenditures = sum(expenditure, na.rm=TRUE)/1000000 ) %>%
pivot_wider(names_from = "group", values_from = "sum_expenditures", names_prefix = "exp_")%>%
left_join(debt_keep_yearly) %>%
mutate(exp_903 = debt_cost) %>%
# left_join(healthcare_costs_yearly) %>%
# join state employee healthcare and subtract employee premiums
# left_join(emp_premium, by = c("fy" = "year")) %>%
# mutate(exp_904_new = (`healthcare_cost` - `employee_premiums_sum`)) %>% # state employee healthcare premiums
# left_join(retirement_contributions) %>%
# mutate(exp_901_new = exp_901 - contributions/1000000) %>% #employee pension contributions
# join local transfers and create exp_970
left_join(transfers) %>%
mutate(exp_970 = exp_971 + exp_972 + exp_975 + exp_976)
ff_exp<- ff_exp %>% select(-c(debt_cost, exp_971:exp_976)) # drop unwanted columns
ff_expCreate total revenues and total expenditures only:
rev_long and exp_long, expenditures and revenues are in the same format and can be combined together for the totals and gap each year.rev_long <- pivot_longer(ff_rev, rev_02:rev_78, names_to = c("type","Category"), values_to = "Dollars", names_sep = "_") %>%
rename(Year = fy) %>%
mutate(Category_name = case_when(
Category == "02" ~ "INDIVIDUAL INCOME TAXES" ,
Category == "03" ~ "CORPORATE INCOME TAXES" ,
Category == "06" ~ "SALES TAXES" ,
Category == "09" ~ "MOTOR FUEL TAX" ,
Category == "12" ~ "PUBLIC UTILITY TAXES" ,
Category == "15" ~ "CIGARETTE TAXES" ,
Category == "18" ~ "LIQUOR GALLONAGE TAXES" ,
Category == "21" ~ "INHERITANCE TAX" ,
Category == "24" ~ "INSURANCE TAXES&FEES&LICENSES" ,
Category == "27" ~ "CORP FRANCHISE TAXES & FEES" ,
Category == "30" ~ "HORSE RACING TAXES & FEES", # in Other
Category == "31" ~ "MEDICAL PROVIDER ASSESSMENTS" ,
Category == "32" ~ "GARNISHMENT-LEVIES" , # dropped
Category == "33" ~ "LOTTERY RECEIPTS" ,
Category == "35" ~ "OTHER TAXES" ,
Category == "36" ~ "RECEIPTS FROM REVENUE PRODUCING",
Category == "39" ~ "LICENSES, FEES & REGISTRATIONS" ,
Category == "42" ~ "MOTOR VEHICLE AND OPERATORS" ,
Category == "45" ~ "STUDENT FEES-UNIVERSITIES", # dropped
Category == "48" ~ "RIVERBOAT WAGERING TAXES" ,
Category == "51" ~ "RETIREMENT CONTRIBUTIONS" , # dropped
Category == "54" ~ "GIFTS AND BEQUESTS",
Category == "57" ~ "FEDERAL OTHER" ,
Category == "58" ~ "FEDERAL MEDICAID",
Category == "59" ~ "FEDERAL TRANSPORTATION" ,
Category == "60" ~ "OTHER GRANTS AND CONTRACTS", #other
Category == "63" ~ "INVESTMENT INCOME", # other
Category == "66" ~ "PROCEEDS,INVESTMENT MATURITIES" , #dropped
Category == "72" ~ "BOND ISSUE PROCEEDS", #dropped
Category == "75" ~ "INTER-AGENCY RECEIPTS ", #dropped
Category == "76" ~ "TRANSFER IN FROM OUT FUNDS", #other
Category == "78" ~ "ALL OTHER SOURCES" ,
Category == "79" ~ "COOK COUNTY IGT", #dropped
Category == "98" ~ "PRIOR YEAR REFUNDS", #dropped
T ~ "Check Me!"
) )%>%
mutate(Category_name = str_to_title(Category_name))
exp_long <- pivot_longer(ff_exp, exp_402:exp_970 , names_to = c("type", "Category"), values_to = "Dollars", names_sep = "_") %>%
rename(Year = fy ) %>%
mutate(Category_name =
case_when(
Category == "402" ~ "AGING" ,
Category == "406" ~ "AGRICULTURE",
Category == "416" ~ "CENTRAL MANAGEMENT",
Category == "418" ~ "CHILDREN AND FAMILY SERVICES",
Category == "420" ~ "COMMERCE AND ECONOMIC OPPORTUNITY",
Category == "422" ~ "NATURAL RESOURCES" ,
Category == "426" ~ "CORRECTIONS",
Category == "427" ~ "EMPLOYMENT SECURITY" ,
Category == "444" ~ "HUMAN SERVICES" ,
Category == "448" ~ "Innovation and Technology", # AWM added fy2022
Category == "478" ~ "FAMILY SERVICES net Medicaid",
Category == "482" ~ "PUBLIC HEALTH",
Category == "492" ~ "REVENUE",
Category == "494" ~ "TRANSPORTATION" ,
Category == "532" ~ "ENVIRONMENTAL PROTECT AGENCY" ,
Category == "557" ~ "IL STATE TOLL HIGHWAY AUTH" ,
Category == "684" ~ "IL COMMUNITY COLLEGE BOARD",
Category == "691" ~ "IL STUDENT ASSISTANCE COMM" ,
Category == "900" ~ "NOT IN FRAME",
Category == "901" ~ "STATE PENSION CONTRIBUTION",
Category == "903" ~ "DEBT SERVICE",
Category == "904" ~ "State Employee Healthcare",
Category == "910" ~ "LEGISLATIVE" ,
Category == "920" ~ "JUDICIAL" ,
Category == "930" ~ "ELECTED OFFICERS" ,
Category == "940" ~ "OTHER HEALTH-RELATED",
Category == "941" ~ "PUBLIC SAFETY" ,
Category == "942" ~ "ECON DEVT & INFRASTRUCTURE" ,
Category == "943" ~ "CENTRAL SERVICES",
Category == "944" ~ "BUS & PROFESSION REGULATION" ,
Category == "945" ~ "MEDICAID" ,
Category == "946" ~ "CAPITAL IMPROVEMENT" ,
Category == "948" ~ "OTHER DEPARTMENTS" ,
Category == "949" ~ "OTHER BOARDS & COMMISSIONS" ,
Category == "959" ~ "K-12 EDUCATION" ,
Category == "960" ~ "UNIVERSITY EDUCATION",
Category == "970" ~ "Local Govt Transfers",
T ~ "CHECK ME!")
) %>%
mutate(Category_name = str_to_title(Category_name))
#write_csv(exp_long, "expenditures_recoded_long_FY22.csv")
#write_csv(rev_long, "revenue_recoded_long_FY22.csv")
aggregated_totals_long <- rbind(rev_long, exp_long)
aggregated_totals_longyear_totals <- aggregated_totals_long %>%
group_by(type, Year) %>%
summarize(Dollars = sum(Dollars, na.rm = TRUE)) %>%
pivot_wider(names_from = "type", values_from = Dollars) %>%
rename(
Expenditures = exp,
Revenue = rev) %>%
mutate(`Fiscal Gap` = round(Revenue - Expenditures))
# %>% arrange(desc(Year))
# creates variable for the Gap each year
year_totals %>%
kbl(caption = "Fiscal Gap for each Fiscal Year") %>%
kable_styling(bootstrap_options = c("striped")) %>%
kable_classic() %>% add_footnote(c("Methodology has changed since past publications","Values include State and Local CURE dollars"))| Year | Expenditures | Revenue | Fiscal Gap |
|---|---|---|---|
| 1998 | 31218.46 | 31264.68 | 46 |
| 1999 | 33804.97 | 33030.25 | -775 |
| 2000 | 37283.05 | 35846.01 | -1437 |
| 2001 | 40300.24 | 37147.74 | -3153 |
| 2002 | 42014.32 | 36825.93 | -5188 |
| 2003 | 42567.14 | 36805.70 | -5761 |
| 2004 | 52980.21 | 40856.24 | -12124 |
| 2005 | 45331.22 | 42865.86 | -2465 |
| 2006 | 48028.45 | 44700.58 | -3328 |
| 2007 | 51098.60 | 48033.25 | -3065 |
| 2008 | 54138.64 | 50213.48 | -3925 |
| 2009 | 56721.05 | 49858.93 | -6862 |
| 2010 | 59247.72 | 49838.70 | -9409 |
| 2011 | 60403.66 | 54731.97 | -5672 |
| 2012 | 59831.15 | 56248.10 | -3583 |
| 2013 | 63261.02 | 60804.22 | -2457 |
| 2014 | 66941.54 | 62772.24 | -4169 |
| 2015 | 69920.58 | 64113.56 | -5807 |
| 2016 | 63909.28 | 61985.56 | -1924 |
| 2017 | 71704.79 | 61349.21 | -10356 |
| 2018 | 74942.57 | 70465.15 | -4477 |
| 2019 | 74383.60 | 72152.87 | -2231 |
| 2020 | 81574.31 | 78141.69 | -3433 |
| 2021 | 92807.11 | 91806.06 | -1001 |
| 2022 | 101829.10 | 113021.57 | 11192 |
| a Methodology has changed since past publications | |||
| b Values include State and Local CURE dollars |
Graphs made from aggregated_totals_long dataframe.
annotation <- data.frame(
x = c(2004, 2017, 2019),
y = c(60000, 50000, 5000),
label = c("Expenditures","Revenue", "Fiscal Gap")
)
# with trend lines:
year_totals %>%
ggplot() +
# geom_smooth adds regression line, graphed first so it appears behind line graph
geom_smooth(aes(x = Year, y = Revenue), color = "rosybrown2", alpha = 0.7, method = "lm", se = FALSE) +
geom_smooth(aes(x = Year, y = Expenditures), color = "gray", method = "lm", se = FALSE) +
# line graph of revenue and expenditures
geom_line(aes(x = Year, y = Revenue), color = "Black", size=1) +
geom_line(aes(x = Year, y = Expenditures), color = "red", size=1) +
# labels
theme_bw() +
scale_y_continuous(labels = comma)+
xlab("Year") +
ylab("Millions of Dollars") +
ggtitle("Illinois Expenditures and Revenue Totals, 1998-2022")
# without trend lines:
year_totals %>%
ggplot() +
# line graph of revenue and expenditures
geom_line(aes(x = Year, y = Revenue), color = "Black", size=1) +
geom_line(aes(x = Year, y = Expenditures), color = "red", size=1) +
theme_bw() +
scale_y_continuous(labels = comma)+
xlab("Year") +
ylab("Millions of Dollars") +
ggtitle("Illinois Expenditures and Revenue Totals, 1998-2022")
fiscal_gap <- year_totals %>%
ggplot() +
geom_hline(yintercept = 0) +
# geom_smooth adds regression line, graphed first so it appears behind line graph
geom_smooth(aes(x = Year, y = Revenue), color = "gray", alpha = 0.7, method = "lm", se = FALSE) +
# scale_linetype_manual(values="dashed")+
geom_smooth(aes(x = Year, y = Expenditures), color ="rosybrown2", linetype = "dashed", method = "lm", se = FALSE, alpha = 0.7) +
# line graph of revenue and expenditures
geom_line(aes(x = Year, y = Revenue), color = "Black", size=1) +
geom_line(aes(x = Year, y = Expenditures, linetype = "dashed"), color = "red", lwd=1) +
geom_line(aes(x = Year, y = `Fiscal Gap`), color = "gray") +
geom_text(data = annotation, aes(x=x, y=y, label=label))+
# labels
theme_bw() +
theme(legend.position = "none")+
scale_linetype_manual(values = c("dashed", "dashed")) +
scale_y_continuous(labels = comma)+
xlab("Year") +
ylab("Millions of Dollars") +
ggtitle("Illinois Expenditures and Revenue Totals, 1998-2022")
fiscal_gap
annotation_billions <- data.frame(
x = c(2004, 2017, 2019),
y = c(60, 50, 5),
label = c("Expenditures","Revenue", "Fiscal Gap")
)
fiscal_gap2 <- year_totals %>%
ggplot() +
geom_hline(yintercept = 0) +
geom_line(aes(x = Year, y = Revenue/1000), color = "Black", lwd=1) +
geom_line(aes(x = Year, y = Expenditures/1000, linetype = "dashed"), color = "red", lwd=1) +
geom_line(aes(x = Year, y = `Fiscal Gap`/1000), color = "gray") +
geom_text(data = annotation, aes(x=x, y=y/1000, label=label))+
theme_bw() +
theme(legend.position = "none")+
scale_linetype_manual(values = c("dashed", "dashed")) +
scale_y_continuous(labels = comma)+
xlab("Year") +
ylab("Billions of Dollars") +
ggtitle("Illinois Expenditures and Revenue Totals, 1998-2022")
fiscal_gap2
aggregated_totals_long %>%
filter(type == "exp") %>% # uses only expenditures
ggplot(aes(x = Year, y = Dollars, group = Category, color = Category)) +
geom_line()+
xlab("Year") +
ylab("Millions of Dollars") +
ggtitle("Illinois Expenditures by Category")
aggregated_totals_long %>%
filter(type == "rev") %>% #uses only revenues
ggplot(aes(x = Year, y = Dollars, group = Category, color = Category)) +
geom_line()+
xlab("Year") +
ylab("Millions of Dollars") +
ggtitle("Illinois Revenues by Category")
Expenditure and revenue amounts in millions of dollars:
exp_long %>%
filter(Year == 2022) %>%
#mutate(`Total Expenditures`= sum(Dollars, na.rm = TRUE)) %>%
# select(-c(Year, `Total Expenditures`)) %>%
arrange(desc(`Dollars`)) %>%
ggplot() +
geom_col(aes(x = fct_reorder(Category_name, `Dollars`), y = `Dollars`, fill = "red"))+
coord_flip() +
theme_bw()+
theme(legend.position = "none") +
labs(title = "Expenditures for FY2022") +
xlab("Expenditure Categories") +
ylab("Millions of Dollars") 
rev_long %>%
filter(Year == 2022) %>%
#mutate(`Total Expenditures`= sum(Dollars, na.rm = TRUE)) %>%
# select(-c(Year, `Total Expenditures`)) %>%
arrange(desc(`Dollars`)) %>%
ggplot() +
geom_col(aes(x = fct_reorder(Category_name, `Dollars`), y = `Dollars`))+
coord_flip() +
theme_bw() +
theme(legend.position = "none") +
labs(title = "Revenues for FY2022")+
xlab("Revenue Categories") +
ylab("Millions of Dollars") 
Expenditure and revenues when focusing on largest categories and combining others into “All Other Expenditures(Revenues)”:
exp_long %>%
filter( Year == 2022) %>%
mutate(rank = rank(Dollars),
Category_name = ifelse(rank > 13, Category_name, 'All Other Expenditures')) %>%
# select(-c(Year, Dollars, rank)) %>%
arrange(desc(Dollars)) %>%
ggplot() +
geom_col(aes(x = fct_reorder(Category_name, `Dollars`), y = `Dollars`), fill = "rosybrown2")+
coord_flip() +
theme_bw() +
labs(title = "Expenditures for FY2022") +
xlab("") +
ylab("Millions of Dollars")
rev_long %>%
filter( Year == 2022) %>%
mutate(rank = rank(Dollars),
Category_name = ifelse(rank > 10, Category_name, 'All Other Sources')) %>%
arrange(desc(Dollars)) %>%
ggplot() +
geom_col(aes(x = fct_reorder(Category_name, `Dollars`), y = `Dollars`), fill = "dark gray")+
coord_flip() +
theme_bw() +
labs(title = "Revenues for FY2022") +
xlab("") +
ylab("Millions of Dollars")
Changes in Categories - 2021 to 2022 Dot Plot Attempt:
rev_long %>%
filter(Year == "2022" | Year == "2021") %>%
mutate(Year = as.character(Year)) %>%
ggplot(aes(x = Dollars, y = reorder(Category, Dollars))) +
geom_line(aes(group = Category) )+
geom_text(aes(x = ifelse(Year == "2022", as.numeric(Dollars), NA), label = ifelse(Year == "2022", Category_name, "")),
hjust = -0.2,
size = 2.8) +
geom_point(aes(color = Year), size=2) +
labs(title = "2021 to 2022 Change in Revenue", x = "Millions of Dollars" , y = "", caption = "") +
scale_fill_manual(values = c("#d62828", "#003049"), labels = c("FY 2021", "FY 2022"))+
scale_color_manual(values = c("#d62828", "#003049")) +
theme_classic()+
theme(
legend.position = "bottom" ,
axis.text.y = element_blank(),
axis.ticks.y = element_blank(),
axis.line.y.left = element_blank(),
# axis.line.x = element_blank(),
# axis.title.y = element_blank(),
# axis.ticks.x = element_blank()
)+
scale_x_continuous(limits = c(0, 31000), labels = comma)
exp_long %>%
filter(Year == "2022" | Year == "2021") %>%
mutate(Year = as.character(Year)) %>%
ggplot(aes(x = Dollars, y = reorder(Category, Dollars))) +
geom_line(aes(group = Category) )+
geom_text(aes(x = ifelse(Year == "2022", (as.numeric(Dollars)+1100), NA),
label = ifelse(Year == "2022", Category_name, "")),
hjust = 0,
size = 2.8) +
geom_point(aes(color = Year), size=2 #, alpha = 0.5
) +
labs(title = "2021 to 2022 Change in Expenditures", x = "Millions of Dollars" , y = "", caption = "") +
scale_fill_manual(values = c("#d62828", "#003049"), labels = c("FY 2021", "FY 2022"))+
scale_color_manual(values = c("#d62828", "#003049")) +
theme_classic()+
theme(
legend.position = "bottom" ,
axis.text.y = element_blank(),
axis.ticks.y = element_blank(),
axis.line.y.left = element_blank(),
#axis.line.x = element_blank(),
# axis.title.y = element_blank(),
#axis.ticks.x = element_blank()
)+
scale_x_continuous(limits = c(0, 31000), labels = comma)
annotation <- data.frame(
x = c(2012, 2019, 2012),
y = c(16000, 10000, 5000),
label = c("Individual Income Tax", "Sales Tax", "Corporate Income Tax")
)
top3 <- rev_long %>%
filter(Category == "02" | Category == "03" | Category == "06") %>%
ggplot()+
geom_line(aes(x=Year, y=Dollars, color = Category_name)) +
geom_text(data = annotation, aes(x=x, y=y, label=label))+
theme_bw() +
scale_y_continuous(labels = comma)+
scale_linetype_manual(values = c("dotted", "dashed", "solid")) +
theme(legend.position = "none")+
labs(title = "Top 3 Own Source Revenues",
subtitle = "Individual Income Taxes, Sales Tax, and Corporate income taxes",
y = "Nominal Dollars (in Millions)")
top3
ownsource_rev <- rev_long %>%
filter(!Category %in% c("57", "58", "59")) %>%
group_by(Year) %>%
summarize(Dollars = sum(Dollars))
# ownsource_rev %>%
# ggplot()+geom_line(aes(x=Year, y=Dollars)) +
# labs(title = "Own Source Revenues", subtitle = "Total own source revenue", y = "Millions of Dollars")
fed_rev <- ff_rev %>% select(fy, rev_57, rev_58, rev_59) %>%
mutate(fed_total = rev_57+rev_58+rev_59)
annotation <- data.frame(
x = c(2010, 2010),
y = c(50000, 25000),
label = c("Own Source Revenue", "Federal Transfers")
)
ggplot() +
geom_line(data = ownsource_rev, aes(x=Year, y=Dollars), color = "Red") +
geom_line(data = fed_rev, aes(x=fy, y=fed_total), color = "Black") +
geom_text(data = annotation, aes(x=x, y=y, label=label))+
scale_y_continuous(labels = comma)+
theme(legend.position = "none")+
theme_bw()+
labs(title = "Own Source Revenue and Federal Transfers",
y = "Nominal Dollars (in Millions)")
Each year, you will need to update the CAGR formulas! Change the filter() year.
calc_cagr is a function created for calculating the CAGRs for different spans of time.
# function for calculating the CAGR
calc_cagr <- function(df, n) {
df <- exp_long %>%
select(-type) %>%
arrange(Category_name, Year) %>%
group_by(Category_name) %>%
mutate(cagr = ((`Dollars` / lag(`Dollars`, n)) ^ (1 / n)) - 1)
return(df)
}
# This works for one variable at a time
cagr_24 <- calc_cagr(exp_long, 24) %>%
# group_by(Category) %>%
summarize(cagr_24 = round(sum(cagr*100, na.rm = TRUE), 2))
cagr23_precovid <- exp_long %>%
filter(Year <= 2019) %>%
calc_cagr(21) %>%
summarize(cagr_21 = round(sum(cagr*100, na.rm = TRUE), 2))
cagr_10 <- calc_cagr(exp_long, 10) %>%
filter(Year == 2022) %>%
summarize(cagr_10 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_5 <- calc_cagr(exp_long, 5) %>%
filter(Year == 2022) %>%
summarize(cagr_5 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_3 <- calc_cagr(exp_long, 3) %>%
filter(Year == 2022) %>%
summarize(cagr_3 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_2 <- calc_cagr(exp_long, 2) %>%
filter(Year == 2022) %>%
summarize(cagr_2 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_1 <- calc_cagr(exp_long, 1) %>%
filter(Year == 2022) %>%
summarize(cagr_1 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
CAGR_expenditures_summary <- data.frame(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_24 ) %>%
select(-c(Category_name.1, Category_name.2, Category_name.3, Category_name.4, Category_name.5 )) %>%
rename("Expenditure Category" = Category_name, "1 Year CAGR" = cagr_1, "2 Year CAGR" = cagr_2, "3 Year CAGR" = cagr_3, "5 Year CAGR" = cagr_5, "10 Year CAGR" = cagr_10,"24 Year CAGR" = cagr_24 )
CAGR_expenditures_summary %>% # adorn_totals("row") %>% # totals calculated numbers, not what I want
kbl(caption = "CAGR Calculations for Expenditure Categories") %>%
kable_styling(bootstrap_options = c("striped"))| Expenditure Category | 1 Year CAGR | 2 Year CAGR | 3 Year CAGR | 5 Year CAGR | 10 Year CAGR | 24 Year CAGR |
|---|---|---|---|---|---|---|
| Aging | 6.35 | 6.87 | 7.18 | -0.65 | 4.33 | 7.49 |
| Agriculture | 43.04 | 15.59 | 8.11 | 6.53 | 3.25 | 1.19 |
| Bus & Profession Regulation | 9.53 | 6.39 | 3.66 | 1.97 | -1.55 | 1.49 |
| Capital Improvement | -6.53 | 17.27 | 18.12 | 10.65 | -3.63 | 2.15 |
| Central Management | 2.05 | 1.06 | 8.53 | 1.18 | 4.71 | 4.46 |
| Children And Family Services | 3.98 | 4.60 | 5.53 | 4.71 | 1.30 | 0.17 |
| Commerce And Economic Opportunity | -15.16 | 51.43 | 35.14 | 16.98 | 3.31 | 4.77 |
| Corrections | 1.52 | 3.16 | 1.13 | 5.12 | 2.48 | 2.13 |
| Debt Service | -0.83 | 1.59 | -0.70 | 1.65 | 1.19 | 6.11 |
| Elected Officers | 7.38 | 7.22 | 3.48 | 6.78 | 4.29 | 3.88 |
| Employment Security | -2.76 | 16.01 | 12.88 | 10.42 | 1.65 | 2.37 |
| Environmental Protect Agency | -1.98 | -4.09 | -7.73 | -6.49 | 0.12 | 3.21 |
| Family Services Net Medicaid | 2.95 | 7.37 | -6.65 | 0.81 | -2.87 | 5.45 |
| Human Services | 15.30 | 12.23 | 10.33 | 6.90 | 3.66 | 2.75 |
| Il State Toll Highway Auth | 7.21 | 4.76 | 6.32 | 3.60 | 11.66 | 7.54 |
| Judicial | 4.20 | 6.41 | 9.15 | 5.11 | 3.40 | 2.99 |
| K-12 Education | 14.51 | 11.07 | 9.44 | 7.39 | 4.53 | 4.30 |
| Legislative | 24.13 | 13.97 | 12.12 | 8.15 | 2.76 | 3.35 |
| Local Govt Transfers | 44.48 | 26.75 | 16.73 | 9.93 | 6.42 | 4.66 |
| Medicaid | 10.11 | 13.93 | 15.00 | 10.14 | 8.99 | 7.25 |
| Natural Resources | 3.90 | 4.22 | 2.19 | 5.39 | 2.85 | 1.76 |
| Other Boards & Commissions | 2.96 | 10.05 | 3.68 | 3.20 | -2.54 | 4.23 |
| Other Departments | 1.94 | 4.84 | 8.22 | 5.63 | 7.06 | 9.10 |
| Public Health | -0.16 | 29.65 | 29.12 | 20.32 | 8.71 | 7.63 |
| Public Safety | -9.74 | 10.35 | 21.41 | 17.00 | 8.62 | 6.11 |
| Revenue | 11.96 | 29.18 | 46.39 | 30.84 | 14.11 | 6.43 |
| State Employee Healthcare | 4.47 | 0.47 | -1.52 | -1.95 | 2.49 | 6.08 |
| State Pension Contribution | 15.42 | 10.80 | 9.67 | 9.26 | 9.38 | 10.76 |
| Transportation | -18.40 | 3.31 | 8.10 | 0.84 | -0.24 | 3.35 |
| University Education | 4.72 | 2.44 | 3.92 | -0.72 | -0.76 | 0.44 |
# to have it as a csv, uncomment the line below
#write_csv(CAGR_expenditures_summary, "CAGR_expenditures_summary.csv")calc_cagr <- function(df, n) {
df <- rev_long %>%
arrange(Category_name, Year) %>%
group_by(Category_name) %>%
mutate(cagr = ((Dollars / lag(Dollars, n)) ^ (1 / n)) - 1)
return(df)
}
# This works for one variable at a time
cagr_24 <- calc_cagr(rev_long, 24) %>%
# group_by(Category) %>%
summarize(cagr_24 = round(sum(cagr*100, na.rm = TRUE), 2))
cagr_10 <- calc_cagr(rev_long, 10) %>%
filter(Year == 2022) %>%
summarize(cagr_10 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_5 <- calc_cagr(rev_long, 5) %>%
filter(Year == 2022) %>%
summarize(cagr_5 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_3 <- calc_cagr(rev_long, 3) %>%
filter(Year == 2022) %>%
summarize(cagr_3 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_2 <- calc_cagr(rev_long, 2) %>%
filter(Year == 2022) %>%
summarize(cagr_2 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_1 <- calc_cagr(rev_long, 1) %>%
filter(Year == 2022) %>%
summarize(cagr_1 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
CAGR_revenue_summary <- data.frame(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_24) %>%
select(-c(Category_name.1, Category_name.2, Category_name.3, Category_name.4, Category_name.5 )) %>%
rename("Revenue Category" = Category_name, "1 Year CAGR" = cagr_1, "2 Year CAGR" = cagr_2, "3 Year CAGR" = cagr_3, "5 Year CAGR" = cagr_5, "10 Year CAGR" = cagr_10,"24 Year CAGR" = cagr_24 )
CAGR_revenue_summary %>%
kbl(caption = "CAGR Calculations for Revenue Sources") %>%
kable_styling(bootstrap_options = c("striped"))| Revenue Category | 1 Year CAGR | 2 Year CAGR | 3 Year CAGR | 5 Year CAGR | 10 Year CAGR | 24 Year CAGR |
|---|---|---|---|---|---|---|
| All Other Sources | 37.70 | 12.92 | 13.64 | 8.08 | 6.28 | 4.54 |
| Cigarette Taxes | -8.25 | -0.54 | 3.02 | 1.49 | 3.33 | 2.51 |
| Corp Franchise Taxes & Fees | -32.40 | 1.21 | -4.37 | 0.85 | 1.18 | 2.55 |
| Corporate Income Taxes | 76.66 | 72.77 | 38.19 | 32.31 | 13.59 | 7.70 |
| Federal Medicaid | 8.48 | 17.30 | 16.43 | 12.76 | 11.30 | 7.52 |
| Federal Other | 110.44 | 41.31 | 48.30 | 26.71 | 11.69 | 7.09 |
| Federal Transportation | -22.95 | 1.39 | 10.40 | -2.73 | -0.06 | 3.33 |
| Gifts And Bequests | 23.76 | 42.11 | 18.49 | 10.46 | 10.65 | 11.43 |
| Individual Income Taxes | 12.60 | 16.35 | 9.25 | 15.22 | 5.36 | 5.68 |
| Inheritance Tax | 35.98 | 48.20 | 16.36 | 18.47 | 10.12 | 3.74 |
| Insurance Taxes&Fees&Licenses | -3.42 | 12.76 | 5.20 | 2.79 | 3.20 | 6.56 |
| Licenses, Fees & Registrations | -4.68 | 15.06 | 16.83 | 9.26 | 6.23 | 7.87 |
| Liquor Gallonage Taxes | 2.53 | 2.81 | 2.49 | 1.69 | 1.37 | 7.45 |
| Lottery Receipts | -6.17 | 9.62 | 1.63 | 2.27 | 0.90 | 2.15 |
| Medical Provider Assessments | -1.98 | 3.67 | 16.26 | 11.80 | 8.33 | 8.36 |
| Motor Fuel Tax | 6.12 | 4.36 | 23.16 | 13.42 | 6.98 | 2.78 |
| Motor Vehicle And Operators | -5.59 | 4.66 | -0.04 | 0.15 | 0.64 | 3.21 |
| Other Taxes | 63.89 | 32.74 | 17.36 | 13.92 | 17.13 | 7.87 |
| Public Utility Taxes | 3.09 | -0.43 | -1.43 | 0.22 | -0.48 | 0.70 |
| Receipts From Revenue Producing | 3.01 | 4.78 | -2.68 | 1.45 | 3.49 | 5.07 |
| Riverboat Wagering Taxes | 80.77 | -1.03 | -8.90 | -6.18 | -4.20 | 1.75 |
| Sales Taxes | 11.29 | 12.22 | 7.40 | 6.27 | 4.43 | 3.23 |
# to have it as a csv, uncomment the line below
#write_csv(CAGR_revenue_summary, "CAGR_revenue_summary.csv")
rm(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_24)Expenditure and Revenue Growth using a lag formula:
exp_long %>%
group_by(Category_name) %>%
mutate(Growth = ((Dollars) - lag(Dollars))/lag(Dollars) *100) %>%
summarize(Growth = round(mean(Growth, na.rm = TRUE), 2)) rev_long %>%
group_by(Category_name) %>%
mutate(Growth = ((Dollars) - lag(Dollars))/lag(Dollars) *100) %>%
summarize(Growth = round(mean(Growth, na.rm = TRUE), 2))revenue_change <- rev_long %>%
select(-c(type,Category)) %>%
filter(Year > 2020) %>%
pivot_wider(names_from = Year , values_from = Dollars, names_prefix = "Dollars_") %>%
mutate(
"FY 2022 Revenues ($ billions)" = round(Dollars_2022/1000, digits = 1),
# "Change from 2021 to 2022" = round(Dollars_2022 - Dollars_2021, digits = 2),
"Percent Change from 2021 to 2022" = round(((Dollars_2022 -Dollars_2021)/Dollars_2021*100), digits = 2)) %>%
left_join(CAGR_revenue_summary, by = c("Category_name" = "Revenue Category")) %>%
arrange(-`FY 2022 Revenues ($ billions)`)%>%
#select(-c(Dollars_2021, Dollars_2021, `1 Year CAGR`:`10 Year CAGR`)) %>%
rename( "Compound Annual Growth, 1998-2022*" = `24 Year CAGR`,
"FY2022 Revenue Category" = Category_name ) %>%
select(-c(Dollars_2021, Dollars_2022, `1 Year CAGR`:`10 Year CAGR`))
revenue_change %>%
kbl(caption = "Yearly Change in Revenue") %>%
kable_styling(bootstrap_options = c("striped"))| FY2022 Revenue Category | FY 2022 Revenues ($ billions) | Percent Change from 2021 to 2022 | Compound Annual Growth, 1998-2022* |
|---|---|---|---|
| Individual Income Taxes | 23.8 | 12.60 | 5.68 |
| Federal Other | 19.4 | 110.44 | 7.09 |
| Federal Medicaid | 19.0 | 8.48 | 7.52 |
| Sales Taxes | 15.4 | 11.29 | 3.23 |
| Corporate Income Taxes | 9.7 | 76.66 | 7.70 |
| Medical Provider Assessments | 3.7 | -1.98 | 8.36 |
| All Other Sources | 2.7 | 37.70 | 4.54 |
| Motor Fuel Tax | 2.5 | 6.12 | 2.78 |
| Receipts From Revenue Producing | 2.4 | 3.01 | 5.07 |
| Licenses, Fees & Registrations | 1.9 | -4.68 | 7.87 |
| Gifts And Bequests | 1.9 | 23.76 | 11.43 |
| Federal Transportation | 1.8 | -22.95 | 3.33 |
| Motor Vehicle And Operators | 1.6 | -5.59 | 3.21 |
| Public Utility Taxes | 1.4 | 3.09 | 0.70 |
| Lottery Receipts | 1.4 | -6.17 | 2.15 |
| Other Taxes | 1.4 | 63.89 | 7.87 |
| Cigarette Taxes | 0.8 | -8.25 | 2.51 |
| Inheritance Tax | 0.6 | 35.98 | 3.74 |
| Insurance Taxes&Fees&Licenses | 0.6 | -3.42 | 6.56 |
| Liquor Gallonage Taxes | 0.3 | 2.53 | 7.45 |
| Riverboat Wagering Taxes | 0.3 | 80.77 | 1.75 |
| Corp Franchise Taxes & Fees | 0.2 | -32.40 | 2.55 |
expenditure_change <- exp_long %>%
select(-c(type,Category)) %>%
filter(Year > 2020) %>%
pivot_wider(names_from = Year , values_from = Dollars, names_prefix = "Dollars_") %>%
mutate("FY 2022 Expenditures ($ billions)" = round(Dollars_2022/1000, digits = 1),
# "Change from 2021 to 2022" = Dollars_2022 - Dollars_2021,
"Percent Change from 2021 to 2022" = round((Dollars_2022 -Dollars_2021)/Dollars_2021*100, digits = 2) )%>%
left_join(CAGR_expenditures_summary, by = c("Category_name" = "Expenditure Category")) %>%
arrange(-`FY 2022 Expenditures ($ billions)`)%>%
select(-c(Dollars_2022, Dollars_2021, `1 Year CAGR`:`10 Year CAGR`)) %>%
rename( "Compound Annual Growth, 1998-2022*" = `24 Year CAGR`,
"FY2022 Expenditure Category" = Category_name )
expenditure_change %>%
kbl(caption = "Yearly Change in Expenditures") %>%
kable_styling(bootstrap_options = c("striped"))| FY2022 Expenditure Category | FY 2022 Expenditures ($ billions) | Percent Change from 2021 to 2022 | Compound Annual Growth, 1998-2022* |
|---|---|---|---|
| Medicaid | 28.9 | 10.11 | 7.25 |
| K-12 Education | 13.9 | 14.51 | 4.30 |
| Local Govt Transfers | 10.4 | 44.48 | 4.66 |
| Human Services | 7.6 | 15.30 | 2.75 |
| State Pension Contribution | 6.5 | 15.42 | 10.76 |
| Other Departments | 4.9 | 1.94 | 9.10 |
| Transportation | 4.4 | -18.40 | 3.35 |
| State Employee Healthcare | 3.0 | 4.47 | 6.08 |
| University Education | 2.3 | 4.72 | 0.44 |
| Il State Toll Highway Auth | 2.1 | 7.21 | 7.54 |
| Debt Service | 2.0 | -0.83 | 6.11 |
| Revenue | 1.9 | 11.96 | 6.43 |
| Public Safety | 1.8 | -9.74 | 6.11 |
| Corrections | 1.6 | 1.52 | 2.13 |
| Children And Family Services | 1.4 | 3.98 | 0.17 |
| Commerce And Economic Opportunity | 1.4 | -15.16 | 4.77 |
| Aging | 1.2 | 6.35 | 7.49 |
| Central Management | 1.2 | 2.05 | 4.46 |
| Elected Officers | 1.0 | 7.38 | 3.88 |
| Public Health | 0.9 | -0.16 | 7.63 |
| Environmental Protect Agency | 0.7 | -1.98 | 3.21 |
| Judicial | 0.5 | 4.20 | 2.99 |
| Family Services Net Medicaid | 0.4 | 2.95 | 5.45 |
| Capital Improvement | 0.4 | -6.53 | 2.15 |
| Natural Resources | 0.3 | 3.90 | 1.76 |
| Employment Security | 0.3 | -2.76 | 2.37 |
| Other Boards & Commissions | 0.3 | 2.96 | 4.23 |
| Bus & Profession Regulation | 0.2 | 9.53 | 1.49 |
| Agriculture | 0.1 | 43.04 | 1.19 |
| Legislative | 0.1 | 24.13 | 3.35 |
Saves main items in one excel file named summary_file.xlsx. Delete eval=FALSE to run on local computer.
#install.packages("openxlsx")
library(openxlsx)
dataset_names <- list('rev_long' = rev_long, 'exp_long' = exp_long,
`Table 1` = expenditure_change, `Table 2` = revenue_change,
'Table 4.a' = CAGR_revenue_summary, 'Table 4.b' = CAGR_expenditures_summary,
'year_totals' = year_totals)
write.xlsx(dataset_names, file = 'summary_file_FY2022.xlsx')If only sustainable revenues are included in the model, then the federal dollars from the pandemic response (CARES, CRSSA,& ARPA)should be excluded from the calculation of the fiscal gap.
The Fiscal Futures model focuses on sustainable revenue sources. To understand our fiscal gap and outlook, we need to exclude these one time revenues. GOMB has emphasized that they have allocated COVID dollars to one time expenditures (unemployment trust fund, budget stabilization fund, etc.). The fiscal gap, graphs,and CAGRs have been recalculated in the Drop COVID Dollars section below. In addition, an attempt at forecasting revenue and expenditures is also made after dropping the federal COVID dollars.
NOTE: I have only dropped State and Local CURE revenue so far. Federal money went into other funds during the beginning of pandemic.
fund 0628 is essential government support services. Money in the fund is appropriated to cover COVID-19 related expenses. It should be included in our analytical frame based on criteria 2 and6 — the fund supports an important state function about public safety, which would have to be performed even the fund structure were not existed. Public safety is supported by a combination of departments and boards, including IL Emergency Management Agency, which is the administering agency of the fund.
Education Stabilization Fund
ESSER
CSLFRF
Provider Relief Fund
Coronavirus Relief Fund (CRF)
Consolidated Appropriations Act
Families First Cornovirus Response Act
Paycheck Protection Program and Health Care Enhancement Act
rev_temp <- rev_temp %>% filter(covid_dollars==0) # keeps observations that were not coded as COVID federal funds
ff_rev <- rev_temp %>%
group_by(rev_type_new, fy) %>%
summarize(sum_receipts = sum(receipts, na.rm=TRUE)/1000000 ) %>%
pivot_wider(names_from = "rev_type_new", values_from = "sum_receipts", names_prefix = "rev_")
ff_rev<- left_join(ff_rev, tax_refund)
ff_rev <- mutate_all(ff_rev, ~replace_na(.,0))
ff_rev <- ff_rev %>%
mutate(rev_02 = rev_02 - ref_02,
rev_03 = rev_03 - ref_03,
rev_06 = rev_06 - ref_06,
rev_09 = rev_09 - ref_09,
rev_21 = rev_21 - ref_21,
rev_24 = rev_24 - ref_24,
rev_35 = rev_35 - ref_35
) %>%
select(-c(ref_02:ref_35, rev_99, rev_NA, rev_76
))
rev_long <- pivot_longer(ff_rev, rev_02:rev_78, names_to = c("type","Category"), values_to = "Dollars", names_sep = "_") %>%
rename(Year = fy) %>%
mutate(Category_name = case_when(
Category == "02" ~ "INDIVIDUAL INCOME TAXES" ,
Category == "03" ~ "CORPORATE INCOME TAXES" ,
Category == "06" ~ "SALES TAXES" ,
Category == "09" ~ "MOTOR FUEL TAX" ,
Category == "12" ~ "PUBLIC UTILITY TAXES" ,
Category == "15" ~ "CIGARETTE TAXES" ,
Category == "18" ~ "LIQUOR GALLONAGE TAXES" ,
Category == "21" ~ "INHERITANCE TAX" ,
Category == "24" ~ "INSURANCE TAXES&FEES&LICENSES" ,
Category == "27" ~ "CORP FRANCHISE TAXES & FEES" ,
Category == "30" ~ "HORSE RACING TAXES & FEES", # in Other
Category == "31" ~ "MEDICAL PROVIDER ASSESSMENTS" ,
Category == "32" ~ "GARNISHMENT-LEVIES" , # dropped
Category == "33" ~ "LOTTERY RECEIPTS" ,
Category == "35" ~ "OTHER TAXES" ,
Category == "36" ~ "RECEIPTS FROM REVENUE PRODUCING",
Category == "39" ~ "LICENSES, FEES & REGISTRATIONS" ,
Category == "42" ~ "MOTOR VEHICLE AND OPERATORS" ,
Category == "45" ~ "STUDENT FEES-UNIVERSITIES", # dropped
Category == "48" ~ "RIVERBOAT WAGERING TAXES" ,
Category == "51" ~ "RETIREMENT CONTRIBUTIONS" , # dropped
Category == "54" ~ "GIFTS AND BEQUESTS",
Category == "57" ~ "FEDERAL OTHER" ,
Category == "58" ~ "FEDERAL MEDICAID",
Category == "59" ~ "FEDERAL TRANSPORTATION" ,
Category == "60" ~ "OTHER GRANTS AND CONTRACTS", #other
Category == "63" ~ "INVESTMENT INCOME", # other
Category == "66" ~ "PROCEEDS,INVESTMENT MATURITIES" , #dropped
Category == "72" ~ "BOND ISSUE PROCEEDS", #dropped
Category == "75" ~ "INTER-AGENCY RECEIPTS ", #dropped
Category == "76" ~ "TRANSFER IN FROM OUT FUNDS", #other
Category == "78" ~ "ALL OTHER SOURCES" ,
Category == "79" ~ "COOK COUNTY IGT", #dropped
Category == "98" ~ "PRIOR YEAR REFUNDS", #dropped
T ~ "Check Me!"
) )%>%
mutate(Category_name = str_to_title(Category_name))
aggregated_totals_long <- rbind(rev_long, exp_long)
year_totals2 <- aggregated_totals_long %>%
group_by(type, Year) %>%
summarize(Dollars = sum(Dollars, na.rm = TRUE)) %>%
pivot_wider(names_from = "type", values_from = Dollars) %>%
rename(
Expenditures = exp,
Revenue = rev) %>%
mutate(`Fiscal Gap` = round(Revenue - Expenditures)) %>%
arrange(desc(Year))
# creates variable for the Gap each year
year_totals2annotation <- data.frame(
x = c(2004, 2017, 2019),
y = c(60000, 50000, 10000),
label = c("Expenditures","Revenue", "Fiscal Gap")
)
fiscal_gap_droppedCURE<-year_totals2 %>%
ggplot() +
geom_hline(yintercept=0)+
# geom_smooth adds regression line, graphed first so it appears behind line graph
geom_smooth(aes(x = Year, y = Revenue), color = "gray", method = "lm", se = FALSE) +
geom_smooth(aes(x = Year, y = Expenditures), color = "rosybrown2", method = "lm", se = FALSE) +
# line graph of revenue and expenditures
geom_line(aes(x = Year, y = Revenue), color = "black", size=1) +
geom_line(aes(x = Year, y = Expenditures), color = "red", size=1) +
geom_line(aes(x=Year, y = `Fiscal Gap`), color="gray") +
geom_text(data= annotation, aes(x=x, y = y, label=label))+
# labels
theme_bw() +
scale_y_continuous(labels = comma)+
xlab("Year") +
ylab("Millions of Dollars") +
ggtitle("Illinois Expenditures and Revenue Totals, 1998-2022")
fiscal_gap_droppedCURE
Compare with and without federal COVID dollars:
library(gridExtra)
cowplot::plot_grid(fiscal_gap, fiscal_gap_droppedCURE)
Expenditure and revenue amounts in millions of dollars:
exp_long %>%
filter(Year == 2022) %>%
#mutate(`Total Expenditures`= sum(Dollars, na.rm = TRUE)) %>%
# select(-c(Year, `Total Expenditures`)) %>%
arrange(desc(`Dollars`)) %>%
ggplot() +
geom_col(aes(x = fct_reorder(Category_name, `Dollars`), y = `Dollars`, fill = "rosybrown2"))+
coord_flip() +
theme_bw() +
theme(legend.position = "none") +
labs(title = "Expenditures for FY2022") +
xlab("Expenditure Categories") +
ylab("Millions of Dollars") 
rev_long %>%
filter(Year == 2022) %>%
#mutate(`Total Expenditures`= sum(Dollars, na.rm = TRUE)) %>%
# select(-c(Year, `Total Expenditures`)) %>%
arrange(desc(`Dollars`)) %>%
ggplot() +
geom_col(aes(x = fct_reorder(Category_name, `Dollars`), y = `Dollars`))+
coord_flip() +
theme_bw() +
labs(title = "Revenues for FY2022")+
xlab("Revenue Categories") +
ylab("Millions of Dollars")
#### Revenues
year_totals2 <- year_totals2 %>%
arrange(Year)
#ts_rev <- year_totals %>% select(Year, Revenue ) %>% arrange(Year)
tsrev <- ts(year_totals2$Revenue, start ="1998", frequency = 1) # yearly data
# start(tsrev) # 1998, January
# end(tsrev) ## 2022
# summary(tsrev)
# plot(tsrev)
# abline(reg=lm(tsrev~time(tsrev)))
#### ARIMAs
mymodel <- auto.arima(tsrev, seasonal = FALSE)
mymodel # ARIMA (0, 1, 0) with driftSeries: tsrev
ARIMA(0,1,1) with drift
Coefficients:
ma1 drift
0.8781 3012.396
s.e. 0.1925 1372.353
sigma^2 = 14506095: log likelihood = -231.63
AIC=469.26 AICc=470.46 BIC=472.79myforecastrev <- forecast(mymodel, h = 20)
#plot(myforecastrev, xlab ="", ylab ="Total Revenue", main ="Chicago Revenue")
tsexp <- ts(year_totals2$Expenditures, start = "1998", frequency = 1)
model_exp<- auto.arima(tsexp, seasonal = FALSE)
model_exp # ARIMA (0,1,1) with driftSeries: tsexp
ARIMA(0,1,0) with drift
Coefficients:
drift
2942.1103
s.e. 866.1814
sigma^2 = 18789310: log likelihood = -234.53
AIC=473.06 AICc=473.63 BIC=475.41forecast_exp <- forecast(model_exp, h = 20)
#plot(forecast_exp, xlab ="", ylab ="Total Expenditures", main ="Chicago Expenditures")
p <- forecast(model_exp, h = 20) %>%
autoplot() +
ylab("Dollars (Millions)") +
xlab("Year") +
ggtitle("Forecasted Expenditures") +
theme_classic() +
scale_y_continuous(labels = dollar )
summary(forecast_exp)
Forecast method: ARIMA(0,1,0) with drift
Model Information:
Series: tsexp
ARIMA(0,1,0) with drift
Coefficients:
drift
2942.1103
s.e. 866.1814
sigma^2 = 18789310: log likelihood = -234.53
AIC=473.06 AICc=473.63 BIC=475.41
Error measures:
ME RMSE MAE MPE MAPE MASE ACF1
Training set 1.131053 4157.663 2668.277 -0.6757971 4.31176 0.6391452 -0.1354505
Forecasts:
Point Forecast Lo 80 Hi 80 Lo 95 Hi 95
2023 104771.2 99216.12 110326.3 96275.43 113267.0
2024 107713.3 99857.23 115569.4 95698.47 119728.2
2025 110655.4 101033.73 120277.1 95940.30 125370.6
2026 113597.5 102487.35 124707.7 96605.97 130589.1
2027 116539.7 104118.08 128961.2 97542.50 135536.8
2028 119481.8 105874.61 133088.9 98671.43 140292.1
2029 122423.9 107726.47 137121.3 99946.14 144901.6
2030 125366.0 109653.80 141078.2 101336.28 149395.7
2031 128308.1 111642.81 144973.4 102820.74 153795.4
2032 131250.2 113683.45 148817.0 104384.17 158116.2
2033 134192.3 115768.15 152616.5 106014.98 162369.6
2034 137134.4 117891.01 156377.8 107704.16 166564.7
2035 140076.5 120047.35 160105.7 109444.55 170708.5
2036 143018.6 122233.38 163803.9 111230.33 174807.0
2037 145960.8 124445.96 167475.5 113056.72 178864.8
2038 148902.9 126682.49 171123.2 114919.73 182886.0
2039 151845.0 128940.73 174749.2 116815.96 186874.0
2040 154787.1 131218.81 178355.4 118742.52 190831.6
2041 157729.2 133515.10 181943.3 120696.93 194761.5
2042 160671.3 135828.17 185514.4 122677.00 198665.6annotation <- data.frame(
x = c(2027, 2032),
y = c(130000, 100000), label = c("$114 ± 19 Billion in 2027","$128 ± 25 Billion in 2032 ")
)
p + geom_label(data = annotation, aes(x=x, y=y, label=label), size = 3) + labs(title = "Forecasted Expenditures", caption = "Projected values at 95% confidence interval.
Dark blue represents 80% liklihood of falling with that range,
light blue represents 95% liklihood of being in projected range.")
#### revenue chart
model_rev <- auto.arima(tsrev, seasonal = FALSE)
forecast_rev <- forecast(model_rev, h = 20)
q <- forecast(forecast_rev, h = 20) %>%
autoplot() +
ylab("Dollars (Millions)") +
xlab("Year") +
ggtitle("Forecasted Revenue") +
theme_classic() +
scale_y_continuous(labels = dollar )
summary(forecast_rev)
Forecast method: ARIMA(0,1,1) with drift
Model Information:
Series: tsrev
ARIMA(0,1,1) with drift
Coefficients:
ma1 drift
0.8781 3012.396
s.e. 0.1925 1372.353
sigma^2 = 14506095: log likelihood = -231.63
AIC=469.26 AICc=470.46 BIC=472.79
Error measures:
ME RMSE MAE MPE MAPE MASE ACF1
Training set 38.18265 3572.865 2673.742 -0.5331993 4.610784 0.7280497 -0.168285
Forecasts:
Point Forecast Lo 80 Hi 80 Lo 95 Hi 95
2023 108159.9 103278.1 113041.8 100693.74 115626.1
2024 111172.3 100786.2 121558.4 95288.11 127056.5
2025 114184.7 100331.5 128037.9 92998.11 135371.3
2026 117197.1 100585.4 133808.8 91791.68 142602.5
2027 120209.5 101236.2 139182.9 91192.27 149226.7
2028 123221.9 102150.0 144293.9 90995.14 155448.7
2029 126234.3 103254.6 149214.0 91089.91 161378.7
2030 129246.7 104505.9 153987.4 91408.98 167084.4
2031 132259.1 105874.6 158643.6 91907.43 172610.8
2032 135271.5 107339.7 163203.2 92553.57 177989.4
2033 138283.9 108886.2 167681.5 93324.07 183243.7
2034 141296.3 110502.4 172090.1 94201.18 188391.4
2035 144308.7 112179.3 176438.1 95170.98 193446.4
2036 147321.1 113909.4 180732.7 96222.37 198419.8
2037 150333.5 115687.0 184979.9 97346.29 203320.7
2038 153345.9 117507.1 189184.6 98535.23 208156.5
2039 156358.3 119365.6 193350.9 99782.92 212933.6
2040 159370.7 121259.1 197482.3 101084.01 217657.3
2041 162383.1 123184.4 201581.7 102433.93 222332.2
2042 165395.5 125139.1 205651.8 103828.73 226962.2annotation <- data.frame(
x = c(2027, 2032),
y = c(200000, 300000),
label = c("$120 billion in 2027","$135 billion in 2032")
)
q+ geom_label(data = annotation, aes(x=x, y=y, label=label), size = 3) +
labs(caption = "after dropping federal covid dollars")
autoplot(tsexp) +
#geom_line(tsexp)+
#geom_line(aes(model_rev))+
autolayer(forecast_rev, series = "Revenue") +
autolayer(forecast_exp, series = "Expenditure)", alpha = 0.5) +
geom_line(year_totals, mapping= aes(x = Year, y = Revenue)) + guides(colour = guide_legend("Forecast")) +
labs(title = "Forecasted Revenue and Expenditures", caption = "Revenue without State and Local CURE Dollars")
Revenue forecasting using precovid trends:
# revenue using precovid trends
tsrev <- ts(year_totals$Revenue, start ="1998", end = "2020", frequency = 1) # yearly data
tsexp2019 <- ts(year_totals$Expenditures, start ="1998", end = "2020", frequency = 1) # yearly data
#### revenue chart
model_rev <- auto.arima(tsrev, seasonal = FALSE)
forecast_rev <- forecast(model_rev, h = 23)
c <- forecast(forecast_rev, h = 22) %>%
autoplot() +
ylab("Dollars (Millions)") +
xlab("Year") +
ggtitle("Forecasted Revenue") +
theme_classic() +
scale_y_continuous(labels = dollar )
summary(forecast_rev)
Forecast method: ARIMA(0,1,0) with drift
Model Information:
Series: tsrev
ARIMA(0,1,0) with drift
Coefficients:
drift
2130.7731
s.e. 522.5883
sigma^2 = 6294284: log likelihood = -202.91
AIC=409.82 AICc=410.45 BIC=412.01
Error measures:
ME RMSE MAE MPE MAPE MASE
Training set 1.266691 2397.281 1730.586 -0.3250278 3.211109 0.7071574
ACF1
Training set -0.08547373
Forecasts:
Point Forecast Lo 80 Hi 80 Lo 95 Hi 95
2021 80272.46 77057.25 83487.67 75355.23 85189.70
2022 82403.24 77856.24 86950.23 75449.21 89357.26
2023 84534.01 78965.10 90102.92 76017.10 93050.92
2024 86664.78 80234.36 93095.20 76830.31 96499.26
2025 88795.56 81606.13 95984.98 77800.28 99790.84
2026 90926.33 83050.71 98801.95 78881.60 102971.05
2027 93057.10 84550.46 101563.75 80047.31 106066.89
2028 95187.88 86093.89 104281.86 81279.83 109095.93
2029 97318.65 87673.02 106964.28 82566.93 112070.36
2030 99449.42 89282.04 109616.81 83899.75 114999.09
2031 101580.20 90916.55 112243.84 85271.56 117888.83
2032 103710.97 92573.16 114848.78 86677.15 120744.78
2033 105841.74 94249.14 117434.34 88112.39 123571.10
2034 107972.51 95942.30 120002.73 89573.89 126371.14
2035 110103.29 97650.84 122555.74 91058.91 129147.67
2036 112234.06 99373.22 125094.90 92565.11 131903.01
2037 114364.83 101108.19 127621.48 94090.54 134639.13
2038 116495.61 102854.63 130136.58 95633.53 137357.68
2039 118626.38 104611.61 132641.15 97192.64 140060.13
2040 120757.15 106378.30 135136.01 98766.59 142747.71
2041 122887.93 108153.99 137621.87 100354.31 145421.54
2042 125018.70 109938.03 140099.37 101954.81 148082.59
2043 127149.47 111729.87 142569.07 103567.23 150731.72annotation <- data.frame(
x = c(2020, 2032),
y = c(130000, 100000),
label = c("$93 ± __ Billion in 2027","$104 ± __ Billion in 2032")
)
c+ geom_label(data = annotation, aes(x=x, y=y, label=label), size = 3) + labs(title= "Revenue Forecasted using Pre-Covid Data", subtitle = "Own Source and Federal Revenues Combined")
autoplot(tsexp2019) +
#geom_line(tsexp)+
#geom_line(aes(model_rev))+
autolayer(forecast_rev, series = "Revenue") +
autolayer(forecast_exp, series = "Expenditure)", alpha = 0.5) +
geom_line(year_totals, mapping= aes(x = Year, y = Revenue)) + guides(colour = guide_legend("Forecast")) +
labs(title = "Forecasted Revenue and Expenditures", caption = "Using Pre-Covid revenue data (ending in FY2020)")
fed_rev <- ff_rev %>% select(fy, rev_57, rev_58, rev_59) %>%
mutate(fed_total = rev_57+rev_58+rev_59)
fed_ts57 <- ts(fed_rev$rev_57, start ="1998", frequency = 1) # yearly data
model_fed <- auto.arima(fed_ts57, seasonal = FALSE)
forecast_fed <- forecast(model_fed, h = 23)
fed57 <- forecast(forecast_fed, h = 20) %>%
autoplot() +
ylab("Nominal Dollars (Millions)") +
xlab("Year") +
ggtitle("Forecasted Federal Other Revenue") +
theme_classic() +
scale_y_continuous(labels = dollar )
fed57
fed_ts58 <- ts(fed_rev$rev_58, start ="1998", frequency = 1) # yearly data
model_fed <- auto.arima(fed_ts58, seasonal = FALSE)
forecast_fed <- forecast(model_fed, h = 23)
fed58 <- forecast(forecast_fed, h = 20) %>%
autoplot() +
ylab("Nominal Dollars (Millions)") +
xlab("Year") +
ggtitle("Forecasted Federal Transfers for Transportation") +
theme_classic() +
scale_y_continuous(labels = dollar )
fed58
fed_ts59 <- ts(fed_rev$rev_59, start ="1998", frequency = 1) # yearly data
model_fed <- auto.arima(fed_ts59, seasonal = FALSE)
forecast_fed <- forecast(model_fed, h = 23)
fed59 <- forecast(forecast_fed, h = 20) %>%
autoplot() +
ylab("Nominal Dollars (Millions)") +
xlab("Year") +
ggtitle("Forecasted Federal Medicaid Reimbursements") +
theme_classic() +
scale_y_continuous(labels = dollar )
fed59
fed_tstotal <- ts(fed_rev$fed_total, start ="1998", frequency = 1) # yearly data
model_fed <- auto.arima(fed_tstotal, seasonal = FALSE)
forecast_fed <- forecast(model_fed, h = 23)
fedtotal <- forecast(forecast_fed, h = 20) %>%
autoplot() +
ylab("Nominal Dollars (Millions)") +
xlab("Year") +
ggtitle("Forecasted Federal Revenue WITHOUT Federal COVID Dollars", subtitle = "Sum of Transportation, Medicaid, and Other Federal Tranfers") +
theme_classic() +
scale_y_continuous(labels = dollar )
fedtotal
fed_tstotal <- ts(fed_rev$fed_total, start ="1998", end = "2020", frequency = 1) # yearly data
model_fed <- auto.arima(fed_tstotal, seasonal = FALSE)
forecast_fed <- forecast(model_fed, h = 23)
fedtotal2 <- forecast(forecast_fed, h = 20) %>%
autoplot() +
ylab("Nominal Dollars (Millions)") +
xlab("Year") +
ggtitle("Forecasted Federal Revenue -- pre-COVID trends", subtitle = "Sum of Transportation, Medicaid, and Other Federal Tranfers") +
theme_classic() +
scale_y_continuous(labels = dollar )
fedtotal2
Graphing the 3 federal revenue types together may be the most reliable since some COVID funding is still recorded in Federal Other and some are in other categories (like Disaster Response in FY2021). Need to look at more before using.
exp_totals <- ff_exp %>% rowwise() %>% mutate(exp_TOTALS = sum(across(exp_402:exp_970)))
rev_totals <- ff_rev %>% rowwise() %>%
mutate(rev_TOTALS = sum(across(rev_02:rev_78)))
rev_long <- pivot_longer(rev_totals, rev_02:rev_TOTALS, names_to = c("type","Category"), values_to = "Dollars", names_sep = "_") %>%
rename(Year = fy) %>%
mutate(Category_name = case_when(
Category == "02" ~ "INDIVIDUAL INCOME TAXES" ,
Category == "03" ~ "CORPORATE INCOME TAXES" ,
Category == "06" ~ "SALES TAXES" ,
Category == "09" ~ "MOTOR FUEL TAX" ,
Category == "12" ~ "PUBLIC UTILITY TAXES" ,
Category == "15" ~ "CIGARETTE TAXES" ,
Category == "18" ~ "LIQUOR GALLONAGE TAXES" ,
Category == "21" ~ "INHERITANCE TAX" ,
Category == "24" ~ "INSURANCE TAXES&FEES&LICENSES" ,
Category == "27" ~ "CORP FRANCHISE TAXES & FEES" ,
Category == "30" ~ "HORSE RACING TAXES & FEES", # in Other
Category == "31" ~ "MEDICAL PROVIDER ASSESSMENTS" ,
Category == "32" ~ "GARNISHMENT-LEVIES" , # dropped
Category == "33" ~ "LOTTERY RECEIPTS" ,
Category == "35" ~ "OTHER TAXES" ,
Category == "36" ~ "RECEIPTS FROM REVENUE PRODUCING",
Category == "39" ~ "LICENSES, FEES & REGISTRATIONS" ,
Category == "42" ~ "MOTOR VEHICLE AND OPERATORS" ,
Category == "45" ~ "STUDENT FEES-UNIVERSITIES", # dropped
Category == "48" ~ "RIVERBOAT WAGERING TAXES" ,
Category == "51" ~ "RETIREMENT CONTRIBUTIONS" , # dropped
Category == "54" ~ "GIFTS AND BEQUESTS",
Category == "57" ~ "FEDERAL OTHER" ,
Category == "58" ~ "FEDERAL MEDICAID",
Category == "59" ~ "FEDERAL TRANSPORTATION" ,
Category == "60" ~ "OTHER GRANTS AND CONTRACTS", #other
Category == "63" ~ "INVESTMENT INCOME", # other
Category == "66" ~ "PROCEEDS,INVESTMENT MATURITIES" , #dropped
Category == "72" ~ "BOND ISSUE PROCEEDS", #dropped
Category == "75" ~ "INTER-AGENCY RECEIPTS ", #dropped
Category == "76" ~ "TRANSFER IN FROM OUT FUNDS", #other
Category == "78" ~ "ALL OTHER SOURCES" ,
Category == "79" ~ "COOK COUNTY IGT", #dropped
Category == "98" ~ "PRIOR YEAR REFUNDS", #dropped
Category == "TOTALS" ~ "Total"
) ) %>%
select(-type, -Category) %>% # drop extra columns type and Category number
group_by(Year, Category_name) %>%
summarise(Dollars= round(sum(Dollars),digits=2)) %>%
mutate(Category_name = str_to_title(Category_name))
# creates wide version of table where each revenue source is a column
revenue_wide2 <- rev_long %>% pivot_wider(names_from = Category_name,
values_from = Dollars) %>%
# relocate("Other Revenue Sources **", .after = last_col()) %>%
relocate("Total", .after = last_col())exp_long <- pivot_longer(exp_totals, exp_402:exp_TOTALS , names_to = c("type", "Category"), values_to = "Dollars", names_sep = "_") %>%
rename(Year = fy ) %>%
mutate(Category_name =
case_when(
Category == "402" ~ "AGING" ,
Category == "406" ~ "AGRICULTURE",
Category == "416" ~ "Central Management",
Category == "418" ~ "CHILDREN AND FAMILY SERVICES",
Category == "420" ~ "Community Development",
Category == "422" ~ "NATURAL RESOURCES" ,
Category == "426" ~ "CORRECTIONS",
Category == "427" ~ "EMPLOYMENT SECURITY" ,
Category == "444" ~ "Human Services" ,
Category == "478" ~ "HEALTHCARE & FAM SER NET OF MEDICAID",
Category == "482" ~ "PUBLIC HEALTH",
Category == "492" ~ "REVENUE",
Category == "494" ~ "Transportation" ,
Category == "532" ~ "ENVIRONMENTAL PROTECT AGENCY" ,
Category == "557" ~ "Tollway" ,
Category == "684" ~ "IL COMMUNITY COLLEGE BOARD",
Category == "691" ~ "IL STUDENT ASSISTANCE COMM" ,
Category == "900" ~ "NOT IN FRAME",
Category == "901" ~ "State Pension Contribution",
Category == "903" ~ "Debt Service",
Category == "904" ~ "State Employee Healthcare",
Category == "910" ~ "LEGISLATIVE" ,
Category == "920" ~ "JUDICIAL" ,
Category == "930" ~ "ELECTED OFFICERS" ,
Category == "940" ~ "OTHER HEALTH-RELATED",
Category == "941" ~ "Public Safety" ,
Category == "942" ~ "ECON DEVT & INFRASTRUCTURE" ,
Category == "943" ~ "CENTRAL SERVICES",
Category == "944" ~ "BUS & PROFESSION REGULATION" ,
Category == "945" ~ "Medicaid" ,
Category == "946" ~ "Capital Improvement" ,
Category == "948" ~ "OTHER DEPARTMENTS" ,
Category == "949" ~ "OTHER BOARDS & COMMISSIONS" ,
Category == "959" ~ "K-12 Education" ,
Category == "960" ~ "UNIVERSITY EDUCATION",
Category == "970" ~ "Local Govt Revenue Sharing",
Category == "TOTALS" ~ "Total") #,T ~ "All Other Expenditures **")
) %>%
select(-type, -Category) %>%
group_by(Year, Category_name) %>%
summarise(Dollars= round(sum(Dollars),digits=2)) %>%
mutate(Category_name = str_to_title(Category_name))
expenditure_wide2 <- exp_long%>%
pivot_wider(names_from = Category_name,
values_from = Dollars) %>%
#relocate("All Other Expenditures **", .after = last_col()) %>%
relocate("Total", .after = last_col())# function for calculating the CAGR
calc_cagr <- function(df, n) {
df <- exp_long %>%
#select(-type) %>%
arrange(Category_name, Year) %>%
group_by(Category_name) %>%
mutate(cagr = ((`Dollars` / lag(`Dollars`, n)) ^ (1 / n)) - 1)
return(df)
}
# This works for one variable at a time
cagr_24 <- calc_cagr(exp_long, 24) %>%
# group_by(Category) %>%
summarize(cagr_24 = round(sum(cagr*100, na.rm = TRUE), 2))
cagr23_precovid <- exp_long %>%
filter(Year <= 2019) %>%
calc_cagr(21) %>%
summarize(cagr_21 = round(sum(cagr*100, na.rm = TRUE), 2))
cagr_10 <- calc_cagr(exp_long, 10) %>%
filter(Year == 2022) %>%
summarize(cagr_10 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_5 <- calc_cagr(exp_long, 5) %>%
filter(Year == 2022) %>%
summarize(cagr_5 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_3 <- calc_cagr(exp_long, 3) %>%
filter(Year == 2022) %>%
summarize(cagr_3 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_2 <- calc_cagr(exp_long, 2) %>%
filter(Year == 2022) %>%
summarize(cagr_2 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_1 <- calc_cagr(exp_long, 1) %>%
filter(Year == 2022) %>%
summarize(cagr_1 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
CAGR_expenditures_summary_tot <- data.frame(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_24 ) %>%
select(-c(Category_name.1, Category_name.2, Category_name.3, Category_name.4, Category_name.5 )) %>%
rename("Expenditure Category" = Category_name, "1 Year CAGR" = cagr_1, "2 Year CAGR" = cagr_2, "3 Year CAGR" = cagr_3, "5 Year CAGR" = cagr_5, "10 Year CAGR" = cagr_10,"24 Year CAGR" = cagr_24 )
move_to_last <- function(df, n) df[c(setdiff(seq_len(nrow(df)), n), n), ]
CAGR_expenditures_summary_tot <- move_to_last(CAGR_expenditures_summary_tot, 29)
#CAGR_expenditures_summary_tot <- select(CAGR_expenditures_summary_tot, -1)
CAGR_expenditures_summary_tot%>%
kbl(caption = "CAGR Calculations for Expenditure Categories" , row.names=FALSE) %>%
kable_classic() %>%
row_spec(31, bold = T, color = "black", background = "gray")| Expenditure Category | 1 Year CAGR | 2 Year CAGR | 3 Year CAGR | 5 Year CAGR | 10 Year CAGR | 24 Year CAGR |
|---|---|---|---|---|---|---|
| Aging | 6.35 | 6.87 | 7.18 | -0.65 | 4.33 | 7.49 |
| Agriculture | 43.05 | 15.59 | 8.10 | 6.53 | 3.25 | 1.19 |
| Bus & Profession Regulation | 9.53 | 6.39 | 3.66 | 1.97 | -1.55 | 1.48 |
| Capital Improvement | -6.53 | 17.27 | 18.12 | 10.65 | -3.63 | 2.15 |
| Central Management | 2.05 | 1.06 | 8.53 | 1.18 | 4.71 | 4.46 |
| Children And Family Services | 3.98 | 4.60 | 5.53 | 4.71 | 1.30 | 0.17 |
| Community Development | -15.16 | 51.43 | 35.14 | 16.98 | 3.31 | 4.77 |
| Corrections | 1.52 | 3.16 | 1.13 | 5.12 | 2.48 | 2.13 |
| Debt Service | -0.83 | 1.59 | -0.70 | 1.65 | 1.19 | 6.11 |
| Elected Officers | 7.38 | 7.22 | 3.48 | 6.78 | 4.29 | 3.88 |
| Employment Security | -2.77 | 16.01 | 12.87 | 10.41 | 1.65 | 2.37 |
| Environmental Protect Agency | -1.98 | -4.09 | -7.73 | -6.49 | 0.12 | 3.21 |
| Healthcare & Fam Ser Net Of Medicaid | 2.95 | 7.37 | -6.65 | 0.81 | -2.87 | 5.45 |
| Human Services | 15.30 | 12.23 | 10.33 | 6.90 | 3.66 | 2.75 |
| Judicial | 4.20 | 6.41 | 9.15 | 5.11 | 3.40 | 2.99 |
| K-12 Education | 14.51 | 11.07 | 9.44 | 7.39 | 4.53 | 4.30 |
| Legislative | 24.13 | 13.97 | 12.12 | 8.15 | 2.76 | 3.35 |
| Local Govt Revenue Sharing | 44.48 | 26.75 | 16.73 | 9.93 | 6.42 | 4.66 |
| Medicaid | 10.11 | 13.93 | 15.00 | 10.14 | 8.99 | 7.25 |
| Natural Resources | 3.90 | 4.22 | 2.19 | 5.39 | 2.85 | 1.76 |
| Other Boards & Commissions | 2.96 | 10.05 | 3.68 | 3.20 | -2.54 | 4.23 |
| Other Departments | 1.94 | 4.84 | 8.22 | 5.63 | 7.06 | 9.10 |
| Public Health | -0.16 | 29.65 | 29.12 | 20.32 | 8.71 | 7.63 |
| Public Safety | -9.74 | 10.35 | 21.41 | 17.00 | 8.62 | 6.11 |
| Revenue | 11.96 | 29.18 | 46.38 | 30.84 | 14.11 | 6.43 |
| State Employee Healthcare | 4.47 | 0.47 | -1.52 | -1.95 | 2.49 | 6.08 |
| State Pension Contribution | 15.42 | 10.80 | 9.67 | 9.26 | 9.38 | 10.76 |
| Tollway | 7.21 | 4.76 | 6.32 | 3.60 | 11.66 | 7.54 |
| Transportation | -18.40 | 3.31 | 8.10 | 0.84 | -0.24 | 3.35 |
| University Education | 4.72 | 2.44 | 3.92 | -0.72 | -0.76 | 0.44 |
| Total | 9.72 | 11.73 | 11.04 | 7.27 | 5.46 | 5.05 |
calc_cagr <- function(df, n) {
df <- rev_long %>%
arrange(Category_name, Year) %>%
group_by(Category_name) %>%
mutate(cagr = ((Dollars / lag(Dollars, n)) ^ (1 / n)) - 1)
return(df)
}
# This works for one variable at a time
cagr_24 <- calc_cagr(rev_long, 24) %>%
# group_by(Category) %>%
summarize(cagr_24 = round(sum(cagr*100, na.rm = TRUE), 2))
cagr_10 <- calc_cagr(rev_long, 10) %>%
filter(Year == 2022) %>%
summarize(cagr_10 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_5 <- calc_cagr(rev_long, 5) %>%
filter(Year == 2022) %>%
summarize(cagr_5 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_3 <- calc_cagr(rev_long, 3) %>%
filter(Year == 2022) %>%
summarize(cagr_3 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_2 <- calc_cagr(rev_long, 2) %>%
filter(Year == 2022) %>%
summarize(cagr_2 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_1 <- calc_cagr(rev_long, 1) %>%
filter(Year == 2022) %>%
summarize(cagr_1 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
CAGR_revenue_summary_tot <- data.frame(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_24) %>%
select(-c(Category_name.1, Category_name.2, Category_name.3, Category_name.4, Category_name.5 )) %>%
rename("Revenue Category" = Category_name, "1 Year CAGR" = cagr_1, "2 Year CAGR" = cagr_2, "3 Year CAGR" = cagr_3, "5 Year CAGR" = cagr_5, "10 Year CAGR" = cagr_10,"24 Year CAGR" = cagr_24 )
CAGR_revenue_summary_tot <- move_to_last(CAGR_revenue_summary_tot,1)
CAGR_revenue_summary_tot <- move_to_last(CAGR_revenue_summary_tot,22)
CAGR_revenue_summary_tot %>%
kbl(caption = "CAGR Calculations for Revenue Sources", row.names = FALSE) %>%
kable_classic() %>%
row_spec(23, bold = T, color = "black", background = "gray")| Revenue Category | 1 Year CAGR | 2 Year CAGR | 3 Year CAGR | 5 Year CAGR | 10 Year CAGR | 24 Year CAGR |
|---|---|---|---|---|---|---|
| Cigarette Taxes | -8.25 | -0.54 | 3.02 | 1.49 | 3.33 | 2.51 |
| Corp Franchise Taxes & Fees | -32.40 | 1.22 | -4.37 | 0.85 | 1.18 | 2.55 |
| Corporate Income Taxes | 76.66 | 72.77 | 38.19 | 32.31 | 13.59 | 7.70 |
| Federal Medicaid | 8.48 | 17.30 | 16.43 | 12.76 | 11.30 | 7.52 |
| Federal Other | 21.32 | 32.72 | 22.40 | 12.92 | 6.39 | 4.55 |
| Federal Transportation | -22.95 | 1.39 | 10.40 | 1.51 | 1.37 | 3.33 |
| Gifts And Bequests | 23.76 | 42.12 | 18.49 | 10.46 | 10.65 | 11.43 |
| Individual Income Taxes | 12.60 | 16.35 | 9.25 | 15.22 | 5.36 | 5.68 |
| Inheritance Tax | 35.98 | 48.20 | 16.36 | 18.47 | 10.12 | 3.74 |
| Insurance Taxes&Fees&Licenses | -3.42 | 12.76 | 5.20 | 2.79 | 3.20 | 6.56 |
| Licenses, Fees & Registrations | -4.68 | 15.06 | 16.83 | 9.26 | 6.23 | 7.87 |
| Liquor Gallonage Taxes | 2.53 | 2.81 | 2.49 | 1.69 | 1.37 | 7.45 |
| Lottery Receipts | -6.17 | 9.62 | 1.63 | 2.27 | 0.90 | 2.15 |
| Medical Provider Assessments | -1.98 | 3.67 | 16.26 | 11.80 | 8.33 | 8.36 |
| Motor Fuel Tax | 6.12 | 4.36 | 23.16 | 13.42 | 6.98 | 2.78 |
| Motor Vehicle And Operators | -5.59 | 4.66 | -0.04 | 0.15 | 0.64 | 3.21 |
| Other Taxes | 63.89 | 32.74 | 17.36 | 13.92 | 17.13 | 7.87 |
| Public Utility Taxes | 3.09 | -0.43 | -1.43 | 0.22 | -0.48 | 0.70 |
| Receipts From Revenue Producing | 3.01 | 4.78 | -2.68 | 1.45 | 3.49 | 5.07 |
| Riverboat Wagering Taxes | 80.77 | -1.03 | -8.90 | -6.18 | -4.20 | 1.75 |
| Sales Taxes | 11.29 | 12.22 | 7.40 | 6.27 | 4.43 | 3.23 |
| All Other Sources | 37.70 | 12.92 | 13.64 | 8.08 | 6.28 | 4.54 |
| Total | 14.15 | 18.36 | 13.15 | 11.40 | 6.55 | 5.16 |
rm(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_24)revenue_change2 <- rev_long %>%
#select(-c(Category)) %>%
filter(Year > 2020) %>%
pivot_wider(names_from = Year , values_from = Dollars, names_prefix = "Dollars_") %>%
mutate(
"FY 2022 Revenues ($ billions)" = round(Dollars_2022/1000, digits = 1),
# "Change from 2021 to 2022" = round(Dollars_2022 - Dollars_2021, digits = 2),
"Percent Change from 2021 to 2022" = round(((Dollars_2022 -Dollars_2021)/Dollars_2021*100), digits = 2)) %>%
left_join(CAGR_revenue_summary_tot, by = c("Category_name" = "Revenue Category")) %>%
arrange(-`FY 2022 Revenues ($ billions)`)%>%
#select(-c(Dollars_2021, Dollars_2021, `1 Year CAGR`:`10 Year CAGR`)) %>%
rename( "Compound Annual Growth, 1998-2022*" = `24 Year CAGR`,
"FY2022 Revenue Category" = Category_name ) %>%
select(-c(Dollars_2021, Dollars_2022, `1 Year CAGR`:`10 Year CAGR`))
revenue_change2 <- move_to_last(revenue_change2,8)
revenue_change2 <- move_to_last(revenue_change2,1)
revenue_change2 %>%
kbl(caption = "Yearly Change in Revenue", row.names = FALSE) %>%
kable_classic() %>%
row_spec(23, bold = T, color = "black", background = "gray")| FY2022 Revenue Category | FY 2022 Revenues ($ billions) | Percent Change from 2021 to 2022 | Compound Annual Growth, 1998-2022* |
|---|---|---|---|
| Individual Income Taxes | 23.8 | 12.60 | 5.68 |
| Federal Medicaid | 19.0 | 8.48 | 7.52 |
| Sales Taxes | 15.4 | 11.29 | 3.23 |
| Federal Other | 10.9 | 21.32 | 4.55 |
| Corporate Income Taxes | 9.7 | 76.66 | 7.70 |
| Medical Provider Assessments | 3.7 | -1.98 | 8.36 |
| Motor Fuel Tax | 2.5 | 6.12 | 2.78 |
| Receipts From Revenue Producing | 2.4 | 3.01 | 5.07 |
| Gifts And Bequests | 1.9 | 23.76 | 11.43 |
| Licenses, Fees & Registrations | 1.9 | -4.68 | 7.87 |
| Federal Transportation | 1.8 | -22.95 | 3.33 |
| Motor Vehicle And Operators | 1.6 | -5.59 | 3.21 |
| Lottery Receipts | 1.4 | -6.17 | 2.15 |
| Other Taxes | 1.4 | 63.89 | 7.87 |
| Public Utility Taxes | 1.4 | 3.09 | 0.70 |
| Cigarette Taxes | 0.8 | -8.25 | 2.51 |
| Inheritance Tax | 0.6 | 35.98 | 3.74 |
| Insurance Taxes&Fees&Licenses | 0.6 | -3.42 | 6.56 |
| Liquor Gallonage Taxes | 0.3 | 2.53 | 7.45 |
| Riverboat Wagering Taxes | 0.3 | 80.77 | 1.75 |
| Corp Franchise Taxes & Fees | 0.2 | -32.40 | 2.55 |
| All Other Sources | 2.7 | 37.70 | 4.54 |
| Total | 104.5 | 14.15 | 5.16 |
expenditure_change2 <- exp_long %>%
#select(-c(type,Category)) %>%
filter(Year > 2020) %>%
pivot_wider(names_from = Year , values_from = Dollars, names_prefix = "Dollars_") %>%
mutate("FY 2022 Expenditures ($ billions)" = round(Dollars_2022/1000, digits = 1),
# "Change from 2021 to 2022" = Dollars_2022 - Dollars_2021,
"Percent Change from 2021 to 2022" = round((Dollars_2022 -Dollars_2021)/Dollars_2021*100, digits = 2) )%>%
left_join(CAGR_expenditures_summary_tot, by = c("Category_name" = "Expenditure Category")) %>%
arrange(-`FY 2022 Expenditures ($ billions)`)%>%
select(-c(Dollars_2022, Dollars_2021, `1 Year CAGR`:`10 Year CAGR`)) %>%
rename( "Compound Annual Growth, 1998-2022*" = `24 Year CAGR`,
"FY2022 Expenditure Category" = Category_name )
expenditure_change2 <- move_to_last(expenditure_change2, 1)
expenditure_change2 %>%
kbl(caption = "Yearly Change in Expenditures", row.names = FALSE) %>%
kable_classic() %>%
row_spec(31, bold = T, color = "black", background = "gray")| FY2022 Expenditure Category | FY 2022 Expenditures ($ billions) | Percent Change from 2021 to 2022 | Compound Annual Growth, 1998-2022* |
|---|---|---|---|
| Medicaid | 28.9 | 10.11 | 7.25 |
| K-12 Education | 13.9 | 14.51 | 4.30 |
| Local Govt Revenue Sharing | 10.4 | 44.48 | 4.66 |
| Human Services | 7.6 | 15.30 | 2.75 |
| State Pension Contribution | 6.5 | 15.42 | 10.76 |
| Other Departments | 4.9 | 1.94 | 9.10 |
| Transportation | 4.4 | -18.40 | 3.35 |
| State Employee Healthcare | 3.0 | 4.47 | 6.08 |
| University Education | 2.3 | 4.72 | 0.44 |
| Tollway | 2.1 | 7.21 | 7.54 |
| Debt Service | 2.0 | -0.83 | 6.11 |
| Revenue | 1.9 | 11.96 | 6.43 |
| Public Safety | 1.8 | -9.74 | 6.11 |
| Corrections | 1.6 | 1.52 | 2.13 |
| Children And Family Services | 1.4 | 3.98 | 0.17 |
| Community Development | 1.4 | -15.16 | 4.77 |
| Aging | 1.2 | 6.35 | 7.49 |
| Central Management | 1.2 | 2.05 | 4.46 |
| Elected Officers | 1.0 | 7.38 | 3.88 |
| Public Health | 0.9 | -0.16 | 7.63 |
| Environmental Protect Agency | 0.7 | -1.98 | 3.21 |
| Judicial | 0.5 | 4.20 | 2.99 |
| Capital Improvement | 0.4 | -6.53 | 2.15 |
| Healthcare & Fam Ser Net Of Medicaid | 0.4 | 2.95 | 5.45 |
| Employment Security | 0.3 | -2.77 | 2.37 |
| Natural Resources | 0.3 | 3.90 | 1.76 |
| Other Boards & Commissions | 0.3 | 2.96 | 4.23 |
| Bus & Profession Regulation | 0.2 | 9.53 | 1.48 |
| Agriculture | 0.1 | 43.05 | 1.19 |
| Legislative | 0.1 | 24.13 | 3.35 |
| Total | 101.8 | 9.72 | 5.05 |
#install.packages("openxlsx")
library(openxlsx)
dataset_names <- list('rev_long' = rev_long, 'exp_long' = exp_long,
`Table 1` = expenditure_change2, `Table 2` = revenue_change2,
'Table 4.a' = CAGR_revenue_summary_tot, 'Table 4.b' = CAGR_expenditures_summary_tot,
'year_totals' = year_totals)
write.xlsx(dataset_names, file = 'summary_file_FY2022_withTotals.xlsx')Export summary file with Totals
dataset_names <- list('Aggregate Revenues' = revenue_wide2,
'Aggregate Expenditures' = expenditure_wide2,
'Table 1' = expenditure_change2, #Top categories with yearly change, 23 yr cagr
'Table 2' = revenue_change2,
# 'Table 4.a' = CAGR_revenue_summary_majorcats, # Categories Match Table 1 in paper
# 'Table 4.b' = CAGR_expenditures_summary_majorcats,
# 'Table 1-AllCats' = expenditure_change_allcats, # All Categories by Year
# 'Table 2-AllCats' = revenue_change_allcats,
'Table 4.a-AllCats' = CAGR_revenue_summary_tot,
'Table 4.b-AllCats' = CAGR_expenditures_summary_tot,
'year_totals' = year_totals, # Total Revenue, Expenditure, and Fiscal gap per year
'aggregated_totals_long' = aggregated_totals_long # all data in long format. Good for creating pivot tables in Excel
)
write.xlsx(dataset_names, file = 'summary_file_FY22_wTotals.xlsx')The 10 largest revenue sources and 13 largest expenditure sources remain separate categories and all other smaller sources/expenditures are combined into “All Other _____”. These condensed tables are typically used in the Fiscal Futures articles. They were manually created in past years but this hopefully automates the process a bit until final formatting stages.
exp_totals <- ff_exp %>% rowwise() %>% mutate(exp_TOTALS = sum(across(exp_402:exp_970))) # creates total column too
rev_totals <- ff_rev %>% rowwise() %>%
mutate(rev_TOTALS = sum(across(rev_02:rev_78)))
rev_long <- pivot_longer(rev_totals, rev_02:rev_TOTALS, names_to = c("type","Category"), values_to = "Dollars", names_sep = "_") %>%
rename(Year = fy) %>%
mutate(Category_name = case_when(
Category == "02" ~ "Income Tax" ,
Category == "03" ~ "Corporate Income Tax" ,
Category == "06" ~ "Sales Tax" ,
Category == "09" ~ "Motor Fuel Taxes" ,
# Category == "12" ~ "PUBLIC UTILITY TAXES, gross of PPRT" ,
# Category == "15" ~ "CIGARETTE TAXES" ,
# Category == "18" ~ "LIQUOR GALLONAGE TAXES" ,
# Category == "21" ~ "INHERITANCE TAX" ,
# Category == "24" ~ "INSURANCE TAXES&FEES&LICENSES, net of refunds " ,
# Category == "27" ~ "CORP FRANCHISE TAXES & FEES" ,
# Category == "30" ~ "HORSE RACING TAXES & FEES", # in Other
Category == "31" ~ "Medical Provider Assessments" ,
# Category == "32" ~ "GARNISHMENT-LEVIES" , # dropped
# Category == "33" ~ "LOTTERY RECEIPTS" ,
# Category == "35" ~ "OTHER TAXES" ,
Category == "36" ~ "Receipts from Revenue Producing",
Category == "39" ~ "Licenses, Fees, Registration" ,
# Category == "42" ~ "MOTOR VEHICLE AND OPERATORS" ,
# Category == "45" ~ "STUDENT FEES-UNIVERSITIES", # dropped
# Category == "48" ~ "RIVERBOAT WAGERING TAXES" ,
# Category == "51" ~ "RETIREMENT CONTRIBUTIONS" , # dropped
# Category == "54" ~ "GIFTS AND BEQUESTS",
Category == "57" ~ "Federal Other" ,
Category == "58" ~ "Federal Medicaid Reimbursements",
Category == "59" ~ "Federal Transportation" ,
# Category == "60" ~ "OTHER GRANTS AND CONTRACTS", #other
# Category == "63" ~ "INVESTMENT INCOME", # other
# Category == "66" ~ "PROCEEDS,INVESTMENT MATURITIES" , #dropped
# Category == "72" ~ "BOND ISSUE PROCEEDS", #dropped
# Category == "75" ~ "INTER-AGENCY RECEIPTS ", #dropped
# Category == "76" ~ "TRANSFER IN FROM OUT FUNDS", #other
# Category == "78new" ~ "ALL OTHER SOURCES" ,
# Category == "79" ~ "COOK COUNTY IGT", #dropped
# Category == "98" ~ "PRIOR YEAR REFUNDS", #dropped
Category == "TOTALS" ~ "Total Revenue",
T ~ "All Other Sources **" # any other Category number that was not specifically referenced is cobined into Other Revenue Sources
) ) %>%
select(-type, -Category) %>% # drop extra columns type and Category number
group_by(Year, Category_name) %>%
summarise(Dollars= round(sum(Dollars),digits=2))
# revenue_wide # not actually in wide format yet.
# has 10 largest rev sources separate and combined all others to Other in long data format.
# creates wide version of table where each revenue source is a column
revenue_wide2 <- rev_long %>% pivot_wider(names_from = Category_name,
values_from = Dollars) %>%
relocate("All Other Sources **", .after = last_col()) %>%
relocate("Total Revenue", .after = last_col())
exp_long <- pivot_longer(exp_totals, exp_402:exp_TOTALS , names_to = c("type", "Category"), values_to = "Dollars", names_sep = "_") %>%
rename(Year = fy ) %>%
mutate(Category_name =
case_when(
# Category == "402" ~ "AGING" ,
# Category == "406" ~ "AGRICULTURE",
Category == "416" ~ "Central Management",
# Category == "418" ~ "CHILDREN AND FAMILY SERVICES",
Category == "420" ~ "Community Development",
# Category == "422" ~ "NATURAL RESOURCES" ,
# Category == "426" ~ "CORRECTIONS",
# Category == "427" ~ "EMPLOYMENT SECURITY" ,
Category == "444" ~ "Human Services" ,
# Category == "478" ~ "HEALTHCARE & FAM SER NET OF MEDICAID",
# Category == "482" ~ "PUBLIC HEALTH",
# Category == "492" ~ "REVENUE",
Category == "494" ~ "Transportation" ,
# Category == "532" ~ "ENVIRONMENTAL PROTECT AGENCY" ,
Category == "557" ~ "Tollway" ,
# Category == "684" ~ "IL COMMUNITY COLLEGE BOARD",
# Category == "691" ~ "IL STUDENT ASSISTANCE COMM" ,
# Category == "900" ~ "NOT IN FRAME",
Category == "901" ~ "State Pension Contribution",
Category == "903" ~ "Debt Service",
Category == "904" ~ "State Employee Healthcare",
# Category == "910" ~ "LEGISLATIVE" ,
# Category == "920" ~ "JUDICIAL" ,
# Category == "930" ~ "ELECTED OFFICERS" ,
# Category == "940" ~ "OTHER HEALTH-RELATED",
Category == "941" ~ "Public Safety" ,
# Category == "942" ~ "ECON DEVT & INFRASTRUCTURE" ,
# Category == "943" ~ "CENTRAL SERVICES",
# Category == "944" ~ "BUS & PROFESSION REGULATION" ,
Category == "945" ~ "Medicaid" ,
Category == "946" ~ "Capital Improvement" ,
# Category == "948" ~ "OTHER DEPARTMENTS" ,
# Category == "949" ~ "OTHER BOARDS & COMMISSIONS" ,
Category == "959" ~ "K-12 Education" ,
# Category == "960" ~ "UNIVERSITY EDUCATION",
Category == "970" ~ "Local Govt Revenue Sharing",
Category == "TOTALS" ~ "Total Expenditures",
T ~ "All Other Expenditures **")
) %>%
select(-type, -Category) %>%
group_by(Year, Category_name) %>%
summarise(Dollars= round(sum(Dollars),digits=2))
expenditure_wide2 <- exp_long%>%
pivot_wider(names_from = Category_name,
values_from = Dollars) %>%
relocate("All Other Expenditures **", .after = last_col()) %>%
relocate("Total Expenditures", .after = last_col())
# CAGR values for largest expenditure categories and combined All Other Expenditures
# function for calculating the CAGR
calc_cagr <- function(df, n) {
df <- exp_long %>%
#select(-type) %>%
arrange(Category_name, Year) %>%
group_by(Category_name) %>%
mutate(cagr = ((`Dollars` / lag(`Dollars`, n)) ^ (1 / n)) - 1)
return(df)
}
# This works for one variable at a time
cagr_24 <- calc_cagr(exp_long, 24) %>%
# group_by(Category) %>%
summarize(cagr_24 = round(sum(cagr*100, na.rm = TRUE), 2))
cagr23_precovid <- exp_long %>%
filter(Year <= 2019) %>%
calc_cagr(21) %>%
summarize(cagr_21 = round(sum(cagr*100, na.rm = TRUE), 2))
cagr_10 <- calc_cagr(exp_long, 10) %>%
filter(Year == 2022) %>%
summarize(cagr_10 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_5 <- calc_cagr(exp_long, 5) %>%
filter(Year == 2022) %>%
summarize(cagr_5 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_3 <- calc_cagr(exp_long, 3) %>%
filter(Year == 2022) %>%
summarize(cagr_3 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_2 <- calc_cagr(exp_long, 2) %>%
filter(Year == 2022) %>%
summarize(cagr_2 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_1 <- calc_cagr(exp_long, 1) %>%
filter(Year == 2022) %>%
summarize(cagr_1 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
CAGR_expenditures_majorcats_tot <- data.frame(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_24 ) %>%
select(-c(Category_name.1, Category_name.2, Category_name.3, Category_name.4, Category_name.5 )) %>%
rename("Expenditure Category" = Category_name, "1 Year CAGR" = cagr_1, "2 Year CAGR" = cagr_2, "3 Year CAGR" = cagr_3, "5 Year CAGR" = cagr_5, "10 Year CAGR" = cagr_10,"24 Year CAGR" = cagr_24 )
move_to_last <- function(df, n) df[c(setdiff(seq_len(nrow(df)), n), n), ]
CAGR_expenditures_majorcats_tot <- move_to_last(CAGR_expenditures_majorcats_tot, 1)
CAGR_expenditures_majorcats_tot <- move_to_last(CAGR_expenditures_majorcats_tot, 13)
CAGR_expenditures_majorcats_tot%>%
kbl(caption = "CAGR Calculations for Largest Expenditure Categories" , row.names=FALSE) %>%
kable_classic() | Expenditure Category | 1 Year CAGR | 2 Year CAGR | 3 Year CAGR | 5 Year CAGR | 10 Year CAGR | 24 Year CAGR |
|---|---|---|---|---|---|---|
| Capital Improvement | -6.53 | 17.27 | 18.12 | 10.65 | -3.63 | 2.15 |
| Central Management | 2.05 | 1.06 | 8.53 | 1.18 | 4.71 | 4.46 |
| Community Development | -15.16 | 51.43 | 35.14 | 16.98 | 3.31 | 4.77 |
| Debt Service | -0.83 | 1.59 | -0.70 | 1.65 | 1.19 | 6.11 |
| Human Services | 15.30 | 12.23 | 10.33 | 6.90 | 3.66 | 2.75 |
| K-12 Education | 14.51 | 11.07 | 9.44 | 7.39 | 4.53 | 4.30 |
| Local Govt Revenue Sharing | 44.48 | 26.75 | 16.73 | 9.93 | 6.42 | 4.66 |
| Medicaid | 10.11 | 13.93 | 15.00 | 10.14 | 8.99 | 7.25 |
| Public Safety | -9.74 | 10.35 | 21.41 | 17.00 | 8.62 | 6.11 |
| State Employee Healthcare | 4.47 | 0.47 | -1.52 | -1.95 | 2.49 | 6.08 |
| State Pension Contribution | 15.42 | 10.80 | 9.67 | 9.26 | 9.38 | 10.76 |
| Tollway | 7.21 | 4.76 | 6.32 | 3.60 | 11.66 | 7.54 |
| Transportation | -18.40 | 3.31 | 8.10 | 0.84 | -0.24 | 3.35 |
| All Other Expenditures ** | 4.13 | 7.59 | 7.95 | 5.28 | 3.69 | 3.68 |
| Total Expenditures | 9.72 | 11.73 | 11.04 | 7.27 | 5.46 | 5.05 |
# Yearly change for Top 13 largest expenditure categories
expenditure_change2 <- exp_long %>%
filter(Year > 2020) %>%
pivot_wider(names_from = Year , values_from = Dollars, names_prefix = "Dollars_") %>%
mutate("FY 2022 Expenditures" = round(Dollars_2022/1000, digits = 1),
"FY 2021 Expenditures" = round(Dollars_2021/1000, digits = 1),
"Percent Change from 2021 to 2022" = percent((Dollars_2022 -Dollars_2021)/Dollars_2021, accuracy = .1) ) %>%
left_join(CAGR_expenditures_majorcats_tot, by = c("Category_name" = "Expenditure Category")) %>%
arrange(-`FY 2022 Expenditures`)%>%
mutate(`24 Year CAGR` = percent(`24 Year CAGR`/100, accuracy=.1)) %>%
select(-c(Dollars_2022, Dollars_2021, `1 Year CAGR`:`10 Year CAGR`)) %>%
rename( "Compound Annual Growth, 1998-2022*" = `24 Year CAGR`,
"FY2022 Expenditure Category" = Category_name )
expenditure_change2 <- move_to_last(expenditure_change2, 3)
expenditure_change2 <- move_to_last(expenditure_change2, 1)
expenditure_change2 %>%
kbl(caption = "Yearly Change in Expenditures", row.names = FALSE, align = "l") %>%
kable_classic() %>%
row_spec(15, bold = T, color = "black", background = "gray")| FY2022 Expenditure Category | FY 2022 Expenditures | FY 2021 Expenditures | Percent Change from 2021 to 2022 | Compound Annual Growth, 1998-2022* |
|---|---|---|---|---|
| Medicaid | 28.9 | 26.3 | 10.1% | 7.2% |
| K-12 Education | 13.9 | 12.2 | 14.5% | 4.3% |
| Local Govt Revenue Sharing | 10.4 | 7.2 | 44.5% | 4.7% |
| Human Services | 7.6 | 6.6 | 15.3% | 2.8% |
| State Pension Contribution | 6.5 | 5.6 | 15.4% | 10.8% |
| Transportation | 4.4 | 5.3 | -18.4% | 3.4% |
| State Employee Healthcare | 3.0 | 2.9 | 4.5% | 6.1% |
| Tollway | 2.1 | 2.0 | 7.2% | 7.5% |
| Debt Service | 2.0 | 2.0 | -0.8% | 6.1% |
| Public Safety | 1.8 | 2.0 | -9.7% | 6.1% |
| Community Development | 1.4 | 1.7 | -15.2% | 4.8% |
| Central Management | 1.2 | 1.2 | 2.0% | 4.5% |
| Capital Improvement | 0.4 | 0.5 | -6.5% | 2.1% |
| All Other Expenditures ** | 18.2 | 17.5 | 4.1% | 3.7% |
| Total Expenditures | 101.8 | 92.8 | 9.7% | 5.0% |
Top 10 revenue sources CAGRs and Yearly Change Tables:
##### Top 10 revenue CAGRs: ####
calc_cagr <- function(df, n) {
df <- rev_long %>%
arrange(Category_name, Year) %>%
group_by(Category_name) %>%
mutate(cagr = ((Dollars / lag(Dollars, n)) ^ (1 / n)) - 1)
return(df)
}
# This works for one variable at a time
cagr_24 <- calc_cagr(rev_long, 24) %>%
# group_by(Category) %>%
summarize(cagr_24 = round(sum(cagr*100, na.rm = TRUE), 2))
cagr_10 <- calc_cagr(rev_long, 10) %>%
filter(Year == 2022) %>%
summarize(cagr_10 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_5 <- calc_cagr(rev_long, 5) %>%
filter(Year == 2022) %>%
summarize(cagr_5 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_3 <- calc_cagr(rev_long, 3) %>%
filter(Year == 2022) %>%
summarize(cagr_3 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_2 <- calc_cagr(rev_long, 2) %>%
filter(Year == 2022) %>%
summarize(cagr_2 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_1 <- calc_cagr(rev_long, 1) %>%
filter(Year == 2022) %>%
summarize(cagr_1 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
CAGR_revenue_majorcats_tot <- data.frame(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_24) %>%
select(-c(Category_name.1, Category_name.2, Category_name.3, Category_name.4, Category_name.5 )) %>%
rename("Revenue Category" = Category_name, "1 Year CAGR" = cagr_1, "2 Year CAGR" = cagr_2, "3 Year CAGR" = cagr_3, "5 Year CAGR" = cagr_5, "10 Year CAGR" = cagr_10,"24 Year CAGR" = cagr_24 )
CAGR_revenue_majorcats_tot <- move_to_last(CAGR_revenue_majorcats_tot,1)
CAGR_revenue_majorcats_tot <- move_to_last(CAGR_revenue_majorcats_tot,11)
CAGR_revenue_majorcats_tot %>%
kbl(caption = "CAGR Calculations for Revenue Sources", row.names = FALSE) %>%
kable_classic() | Revenue Category | 1 Year CAGR | 2 Year CAGR | 3 Year CAGR | 5 Year CAGR | 10 Year CAGR | 24 Year CAGR |
|---|---|---|---|---|---|---|
| Corporate Income Tax | 76.66 | 72.77 | 38.19 | 32.31 | 13.59 | 7.70 |
| Federal Medicaid Reimbursements | 8.48 | 17.30 | 16.43 | 12.76 | 11.30 | 7.52 |
| Federal Other | 21.32 | 32.72 | 22.40 | 12.92 | 6.39 | 4.55 |
| Federal Transportation | -22.95 | 1.39 | 10.40 | 1.51 | 1.37 | 3.33 |
| Income Tax | 12.60 | 16.35 | 9.25 | 15.22 | 5.36 | 5.68 |
| Licenses, Fees, Registration | -4.68 | 15.06 | 16.83 | 9.26 | 6.23 | 7.87 |
| Medical Provider Assessments | -1.98 | 3.67 | 16.26 | 11.80 | 8.33 | 8.36 |
| Motor Fuel Taxes | 6.12 | 4.36 | 23.16 | 13.42 | 6.98 | 2.78 |
| Receipts from Revenue Producing | 3.01 | 4.78 | -2.68 | 1.45 | 3.49 | 5.07 |
| Sales Tax | 11.29 | 12.22 | 7.40 | 6.27 | 4.43 | 3.23 |
| All Other Sources ** | 13.85 | 13.43 | 6.90 | 4.95 | 4.07 | 3.90 |
| Total Revenue | 14.15 | 18.36 | 13.15 | 11.40 | 6.55 | 5.16 |
###### Yearly change summary table for Top 10 Revenues #####
revenue_change2 <- rev_long %>%
#select(-c(Category)) %>%
filter(Year > 2020) %>%
pivot_wider(names_from = Year , values_from = Dollars, names_prefix = "Dollars_") %>%
mutate(
"FY 2022 Revenues ($ billions)" = round(Dollars_2022/1000, digits = 1),
"FY 2021 Revenues ($ billions)" = round(Dollars_2021/1000, digits = 1),
"Percent Change from 2021 to 2022" = percent(((Dollars_2022 -Dollars_2021)/Dollars_2021), accuracy = .1)) %>%
left_join(CAGR_revenue_majorcats_tot, by = c("Category_name" = "Revenue Category")) %>%
arrange(-`FY 2022 Revenues ($ billions)`)%>%
#select(-c(Dollars_2021, Dollars_2021, `1 Year CAGR`:`10 Year CAGR`)) %>%
mutate("Compound Annual Growth, 1998-2022*" = percent(`24 Year CAGR`/100, accuracy=.1)) %>%
rename("FY2022 Revenue Category" = Category_name ) %>%
select(-c(Dollars_2022, Dollars_2021, `1 Year CAGR`:`24 Year CAGR`))
revenue_change2 <- move_to_last(revenue_change2,5)
revenue_change2 <- move_to_last(revenue_change2,1)
revenue_change2%>%
kbl(caption = "Yearly Change in Revenue", row.names = FALSE, align = "l") %>%
kable_classic() %>%
row_spec(12, bold = T, color = "black", background = "gray")| FY2022 Revenue Category | FY 2022 Revenues ($ billions) | FY 2021 Revenues ($ billions) | Percent Change from 2021 to 2022 | Compound Annual Growth, 1998-2022* |
|---|---|---|---|---|
| Income Tax | 23.8 | 21.2 | 12.6% | 5.7% |
| Federal Medicaid Reimbursements | 19.0 | 17.6 | 8.5% | 7.5% |
| Sales Tax | 15.4 | 13.9 | 11.3% | 3.2% |
| Federal Other | 10.9 | 9.0 | 21.3% | 4.6% |
| Corporate Income Tax | 9.7 | 5.5 | 76.7% | 7.7% |
| Medical Provider Assessments | 3.7 | 3.8 | -2.0% | 8.4% |
| Motor Fuel Taxes | 2.5 | 2.4 | 6.1% | 2.8% |
| Receipts from Revenue Producing | 2.4 | 2.3 | 3.0% | 5.1% |
| Licenses, Fees, Registration | 1.9 | 2.0 | -4.7% | 7.9% |
| Federal Transportation | 1.8 | 2.4 | -22.9% | 3.3% |
| All Other Sources ** | 13.3 | 11.7 | 13.9% | 3.9% |
| Total Revenue | 104.5 | 91.6 | 14.2% | 5.2% |
Saves main items in one excel file named summary_file.xlsx. Delete eval=FALSE to run on local computer.
#install.packages("openxlsx")
library(openxlsx)
dataset_names <- list('Aggregate Revenues' = revenue_wide2, # Top Categories aggregated, nice labels
'Aggregate Expenditures' = expenditure_wide2,
'Table 1' = expenditure_change2, #Top categories with yearly change, 23 yr cagr
'Table 2' = revenue_change2,
'Table 4.a' = CAGR_revenue_summary_majorcats, # Categories Match Table 1 in paper
'Table 4.b' = CAGR_expenditures_summary_majorcats,
'Table 1-AllCats' = expenditure_change_allcats, # All Categories by Year
'Table 2-AllCats' = revenue_change_allcats,
'Table 4.a-AllCats' = CAGR_revenue_summary_allcats,
'Table 4.b-AllCats' = CAGR_expenditures_summary_allcats,
'year_totals' = year_totals, # Total Revenue, Expenditure, and Fiscal gap per year
'aggregated_totals_long' = aggregated_totals_long # all data in long format. Good for creating pivot tables in Excel
)
write.xlsx(dataset_names, file = 'summary_file_FY22_MajorCats_WithTotals.xlsx')